How to calculate delta (change) between the most recent and previous event(s)

[Anonymous]

Hi all,

In the early stages of building a new data model and report. We will have a table that a Quantity column that report total cumlative values, not incremental values. I would like calculate the change between each "event". There will be a date column, but the events are not at a specific time interval. Furthermore there are categories/classifications of each "event". Here is a simple sample table represenative of the data (the data model that doesn't exsist yet, will be a full star schema):

Sample Table

ID	Option	Category	Date	Quantity	Delta (calculated)
1	Option1	CatA	10/27/2020	500	0
2	Option1	CatB	10/27/2020	1000	0
3	Option1	CatA	10/27/2025	1000	500
4	Option1	CatB	10/27/2030	1500	500
5	Option1	CatA	10/27/2035	4500	3500
6	Option1	CatB	10/27/2035	1250	-250
7	Option1	CatA	10/27/2050	11200	6700
8	Option1	CatB	10/27/2050	1700	450
9	Option2	CatA	10/27/2020	500	0
10	Option2	CatB	10/27/2020	1000	0
11	Option2	CatA	10/27/2025	1250	750
12	Option2	CatB	10/27/2030	2000	1000
13	Option2	CatA	10/27/2035	2000	750
14	Option2	CatB	10/27/2035	2500	500
15	Option2	CatA	10/27/2050	3000	1000
16	Option2	CatB	10/27/2050	2100	-400

The last row illustrates the value(s) I'd like to be able to calculate and chart accordingly or do further analysis. One example I found online suggested using a sequential index column added in Power Query, but I have the Date column, so I'd rather not add the additional index column if I don't need to. While in my sample here the ID column is sequential with date order, that is not garunteed in the production data set.

[AlB]

Hi @Anonymous 

Date would be sufficient if there weren't repeated dates per Category/Option.  You need an index. Make sure it establishes the order correctly. The one in your table seems wrong. Try this:

Delta column =
VAR previousIndex_ =
    CALCULATE (
        MAX ( Table1[Date] ),
        Table1[Index] < EARLIER ( Table1[Index] ),
        ALLEXCEPT ( Table1, Table1[Category], Table1[Option] )
    )
VAR previousVal_ =
    CALCULATE (
        DISTINCT ( Table1[Quantity] ),
        Table1[Date] = previousIndex_,
        ALLEXCEPT ( Table1, Table1[Category], Table1[Option] )
    )
VAR currentVal_ = Table1[Quantity]
RETURN
    currentVal_ - previousVal_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB thanks for the response.

So you're confirming that there is no good way to filter to only rows pertaining to the context defined by the current Option and Category, and then based on the current date value in the current context, find the next most recent date?

You're correct, the ID column is not sequential with the Date values nor would I expect it to be in the data I'll be getting. Adding an index column with the data sorted by date in PowerQuery should not be an issue. I had read that adding index columns can be a bit of a performance hit for large datasets (not that that is an issue for me in this case), but I like to do things the "right" way as much as possible, build good habbits and all that.

You're also correct the dates will overlap. The sample data is more granular than what I actually expect. I expect most of my data will be granular to the YYYY (as in either 01/01/YYYY or 12/31/YYYY). There is a possibility that the date date might be granular to the Month, but that is a big maybe at the moment. If that is the case it would all likely look like MM/01/YYYY (since months don't have the same number of days...).

On first pass the DAX looks relatively straight-forward, appreciate the assistance.

Other comments, thoughts more than welcome.

Thank you.

[AlB]

---

@Anonymous wrote:

So you're confirming that there is no good way to filter to only rows pertaining to the context defined by the current Option and Category, and then based on the current date value in the current context, find the next most recent date?

@Anonymous 

Not confirming at all. That is pretty much what the code above does. But because you have more than one row per date (in some cases) per Category/Option and you seen to want to have an additional order for those rows with the same date, you need that index. Otherwise those rows will be indistinguishable internally, in terms of order, for the calc we are performing.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers