Below is the code i developed and i have provided the comments in each section why i do it. Hope you will read this easily.
M_Total_Days_Since_LastIncident =
VAR maxDate =
MAX ( dimDate[FullDate] ) -- Maximum date selected in parameter date range
VAR maxDEP1 =
CALCULATE (
MAX ( FactDep1Event[EventDateKey] ),
FILTER ( ALL ( dimDate ), dimDate[FullDate] <= maxDate )
) -- Last event datekey for Dep1 on or before maximum date selected
VAR maxDEP1Date =
CALCULATE (
MAX ( dimDate[FullDate] ),
FILTER ( ALL ( dimdate ), dimDate[DateKey] = maxDEP1 )
) -- Find the date associated with dep1
VAR maxDEP2 =
CALCULATE (
MAX ( FactDEP2Event[EventDateKey] ),
FILTER ( ALL ( dimDate ), dimDate[FullDate] <= maxDate )
) -- Last event datekey for Dep2 on or before maximum date selected
VAR maxDep2Date =
CALCULATE (
MAX ( dimDate[FullDate] ),
FILTER ( ALL ( dimdate ), dimDate[DateKey] = maxDEP2 )
) -- Find the date associated with dep2
RETURN
IF (
ISBLANK ( maxDEP1Date ) && ISBLANK ( maxDep2Date ),
"-",
IF (
maxDEP1Date >= maxDep2Date ,
DATEDIFF ( maxDEP1Date , maxDate, DAY ) + 0,
DATEDIFF ( maxDep2Date , maxDate, DAY ) + 0
)
)-- Compare which date is closer to maximum date and display
No comments:
Post a Comment