Forum Discussion

sam_gift's avatar
sam_gift
Icon for Helper I rankHelper I
2 years ago
Solved

Date Difference between snapshots based on a column

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's avatar
    Jihwan_Kim
    2 years ago

    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's avatar
    AntrikshSharma
    2 years ago

    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:

     

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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: