topic Re: Date Difference between snapshots based on a column in DAX Commands and Tips

Date Difference between snapshots based on a column

sam_gift — Tue, 20 Aug 2024 00:23:36 GMT

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

Re: Date Difference between snapshots based on a column

Jihwan_Kim — Tue, 20 Aug 2024 05:13:14 GMT

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 ) )

Re: Date Difference between snapshots based on a column

AntrikshSharma — Tue, 20 Aug 2024 05:58:27 GMT

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: