How to calculate rolling difference grouped by other column values?

nivek29 — Tue, 31 Jan 2023 09:11:02 GMT

Hi together,

I have the following data where each Date stores a number of Orders per single FiscalYearQuarter (simplified). Each FiscalYearQuarter is stored within each Date, because we want to track if there are subsequent modifications taking place in the data.

FiscalYearQuarter	Orders	Date
Q4-2021	10	2023/01/30
Q3-2021	8	2023/01/30
Q2-2021	12	2023/01/30
Q1-2021	14	2023/01/30
Q4-2021	7	2023/01/29
Q3-2021	4	2023/01/29
Q2-2021	9	2023/01/29
Q1-2021	5	2023/01/29
Q4-2021	6	2023/01/28
Q3-2021	3	2023/01/28
Q2-2021	12	2023/01/28
Q1-2021	15	2023/01/28

My goal is to calculate the rolling difference of the sum of Orders between the current and previous Date within a single FiscalYearQuarter. E.g., I need to calculate how the sum of Orders belonging to FiscalYearQuarter Q2-2021 has changed over the course of Date (as indicated in purple).

My previous attempts were not successful:

Delta = VAR PreviousDate = Data[Date] - 1 RETURN Data[Orders] - SUMX(FILTER(Data, Data[Date] = PreviousDate, Data[Orders])

I hope this is a starting point. What's needs to be added is that I need the rolling difference (delta) between each two Dates and not only as a total measure, so that I can illustrate it as a line diagram with Date as x-axis in oder to track delta over time. Therefore, a calculated column might be necessary. Thanks in advance!

Re: How to calculate rolling difference grouped by other column values?

nivek29 — Tue, 31 Jan 2023 11:25:18 GMT

Note: I also experimented with trying to group by the Fiscal Year Quarters, but so far I do not get any meaningful resulting values

topic Re: How to calculate rolling difference grouped by other column values? in DAX Commands and Tips

How to calculate rolling difference grouped by other column values?

Re: How to calculate rolling difference grouped by other column values?