Date Difference between snapshots based on a column

[sam_gift]

Hi, Please help with the dax for below.

I have a sample data here. I need to compare the date for the same ID of snapshot -1 with snapshot seq -2, and give the days different. If the date is same then 0, if not no.of days difference between the snapshots

For example ID A1 have the date '2/8/2024/ for -1 and /7/8/2024 for -2. so the day diff will be 151 days. similarly for B1, C1 and so on. Please help me with the dax

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: = 
VAR _seqone = -1
VAR _seqtwo = -2
VAR _dayone =
    MAXX(
        FILTER( data, data[snapshot_seq] = _seqone ),
        data[date]
    )
VAR _daytwo =
    MAXX(
        FILTER( data, data[snapshot_seq] = _seqtwo ),
        data[date]
    )
RETURN
    IF(
        HASONEVALUE( 'id'[id] ),
        DATEDIFF( _dayone, _daytwo, DAY )
    )

[AntrikshSharma · Accepted solution]

@sam_gift If you want a calculated column in your table then you can try this:

Column = 
VAR CurrentSnapshotSeq = 
    Sam[SnapshotSeq]
VAR CurrentDate = 
    Sam[Date]
VAR PreviousDate = 
    CALCULATE ( 
        MIN ( Sam[Date] ),
        Sam[SnapshotSeq] > CurrentSnapshotSeq,
        ALLEXCEPT ( Sam, Sam[ID] )
    )
VAR Result = 
    IF ( 
        PreviousDate,
        INT ( CurrentDate - PreviousDate )
    )
RETURN Result

For a measure in a visual you can change it a little bit:

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: = 
VAR _seqone = -1
VAR _seqtwo = -2
VAR _dayone =
    MAXX(
        FILTER( data, data[snapshot_seq] = _seqone ),
        data[date]
    )
VAR _daytwo =
    MAXX(
        FILTER( data, data[snapshot_seq] = _seqtwo ),
        data[date]
    )
RETURN
    IF(
        HASONEVALUE( 'id'[id] ),
        DATEDIFF( _dayone, _daytwo, DAY )
    )