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