Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dates Between calculation based on previous row

Hi team,   I need help with a DatesBetween DAX calculation. I have data coming from two tables: Table 1: CodeKey                    CodeNum 1   PM033 2   PM034 3   ...
Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Days between measure: =
VAR currentcode =
    MAX ( Table1[CodeKey] )
VAR currentdate =
    MAX ( Table2[Date] )
VAR previousdate =
    MAXX (
        FILTER (
            ALL ( Table2 ),
            Table2[CodeKey] = currentcode
                && Table2[Date] < currentdate
        ),
        Table2[Date]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[CodeKey] ),
        INT ( currentdate - previousdate )
            * DIVIDE ( previousdate, previousdate )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

Days between measure: =
VAR currentcode =
    MAX ( Table1[CodeKey] )
VAR currentdate =
    MAX ( Table2[Date] )
VAR previousdate =
    MAXX (
        FILTER (
            ALL ( Table2 ),
            Table2[CodeKey] = currentcode
                && Table2[Date] < currentdate
        ),
        Table2[Date]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[CodeKey] ),
        INT ( currentdate - previousdate )
            * DIVIDE ( previousdate, previousdate )
    )