topic Re: Using DAX to get delta for consecutive snapshots... in DAX Commands and Tips

Using DAX to get delta for consecutive snapshots...

Anonymous — Mon, 24 Oct 2022 22:59:10 GMT

I need to get a running daily delta of consecutive snapshots for certain dimensions/facts. So, let's say I have three Dims:

Customer, Part, Group

and two Facts:

Shipments, Orders

Every day I'm going to take a snapshot of these Dims/Facts and give them a SnapshotDate (i.e. the date the snapshot was taken). I want to plot SnapshotDate on a chart and show the following as the value:

SUM of a given Fact (at any given Dimension) for each SnapshotDate MINUS the SUM for that same Fact (at those same Dimensions) for the PRIOR SnapshotDate.

So, the 10/24 delta would be (10/24 - 10/23), the 10/23 delta would be (10/23 - 10/22), and so on...

Is this possible using DAX? I could do it with SQL, but I'm trying to make use of the existing data in the Dataset instead of pulling in Deltas as data.

Re: Using DAX to get delta for consecutive snapshots...

v-yinliw-msft — Tue, 25 Oct 2022 09:15:46 GMT

Hi Anonymous ,

Could you please provide me some sample data for testing? Please delete the private data.

And could I ask that what kind of result do you want? A visual or a table?

Best Regards,

Community Support Team _Yinliw

Re: Using DAX to get delta for consecutive snapshots...

Anonymous — Tue, 25 Oct 2022 20:21:52 GMT

Thanks but I figured it out. For anyone who needs it, I added a Measure like so:

Next Snapshot Orders = var nxt = CALCULATE(MIN(MySnapshots[SnapshotDate]), FILTER(MySnapshots, MySnapshots[SnapshotDate]>SELECTEDVALUE(MySnapshots[SnapshotDate]))) RETURN CALCULATE(SUM(MySnapshots[Orders]), REMOVEFILTERS(MySnapshots[SnapshotDate]), MySnapshots[SnapshotDate]=nxt)

It gets the snapshot date after the current selectedvalue (which will be determined by the SnapshotDate in my Chart/Table, but it could just as easily be determined by a selection in a slicer or whatever).

Then I SUM the "Orders" from the snapshot tables for the NEXT snapshot, removing ONLY the SnapshotDate filter from the current table. This way, all of my other criteria (in my chart/table/page/etc) is retained and the only thing I'm changing is which Snapshot I'm using to get my SUM.

This Measure only shows how to get the total for the "next" snapshot's Orders. That's not everything I mentioned above, because ultimately I'm going to combine Shipments and Orders for the "next" snapshot and compare them to Shipments/Orders from the "selected" snapshot (in the chart/table) and calc the delta, but this is the bulk of what is needed to do what I need. All I have to do to see the current snapshot's Orders vs the next snapshot's Orders is create a table with SnapshotDate on it, add Orders, then add my Next Snapshot Orders measure and I can see them side-by-side.