Calculate difference between current and previous row with a condition

Sultanista — Mon, 14 Nov 2022 13:53:42 GMT

I have a table and need to calculate a difference between 2 rows with a condition that it only applies to Agregation Type = All Branches - YTD and it is a diff between fiscal month and fiscal month-1.

In the table I added a column Diff that should show those results. # of rows will grow and is added on a weekly basis.

Link_key	Fiscal Year	Fiscal Month	Fiscal Week	Agregation Type	Applicants	Diff
2021-1-All Branches - YTD	2021	1	4	All Branches - YTD	25 840
2021-2-All Branches - YTD	2021	2	8	All Branches - YTD	50 133	24 293
2021-3-All Branches - YTD	2021	3	13	All Branches - YTD	75 089	24 956
2021-4-All Branches - YTD	2021	4	17	All Branches - YTD	94 888	19 799
2021-5-All Branches - YTD	2021	5	21	All Branches - YTD	117 866	22 978
2021-6-All Branches - YTD	2021	6	26	All Branches - YTD	151 612	33 746
2021-7-All Branches - YTD	2021	7	30	All Branches - YTD	183 447	31 835
2021-8-All Branches - YTD	2021	8	34	All Branches - YTD	217 275	33 828
2021-9-All Branches - YTD	2021	9	39	All Branches - YTD	262 469	45 194
2021-10-All Branches - YTD	2021	10	43	All Branches - YTD	303 900	41 431
2021-11-All Branches - YTD	2021	11	47	All Branches - YTD	343 324	39 424
2021-12-All Branches - YTD	2021	12	52	All Branches - YTD	386 621	43 297
2022-1-All Branches - YTD	2022	1	4	All Branches - YTD	37 203
2022-2-All Branches - YTD	2022	2	8	All Branches - YTD	69 594	32 391
2022-3-All Branches - YTD	2022	3	13	All Branches - YTD	106 129	36 535
2022-4-All Branches - YTD	2022	4	17	All Branches - YTD	139 629	33 500
2022-5-All Branches - YTD	2022	5	21	All Branches - YTD	175 917	36 288
2022-6-All Branches - YTD	2022	6	26	All Branches - YTD	227 160	51 243
2022-7-All Branches - YTD	2022	7	30	All Branches - YTD	269 020	41 860
2022-8-All Branches - YTD	2022	8	34	All Branches - YTD	306 487	37 467
2022-9-All Branches - YTD	2022	9	39	All Branches - YTD	354 041	47 554
2021-1-All Branches - MTD	2021	1	4	All Branches - MTD
2021-2-All Branches - MTD	2021	2	8	All Branches - MTD
2021-3-All Branches - MTD	2021	3	13	All Branches - MTD
2021-4-All Branches - MTD	2021	4	17	All Branches - MTD
2021-5-All Branches - MTD	2021	5	21	All Branches - MTD
2021-6-All Branches - MTD	2021	6	26	All Branches - MTD
2021-7-All Branches - MTD	2021	7	30	All Branches - MTD
2021-8-All Branches - MTD	2021	8	34	All Branches - MTD
2021-9-All Branches - MTD	2021	9	39	All Branches - MTD
2021-10-All Branches - MTD	2021	10	43	All Branches - MTD
2021-11-All Branches - MTD	2021	11	47	All Branches - MTD
2021-12-All Branches - MTD	2021	12	52	All Branches - MTD
2022-1-All Branches - MTD	2022	1	4	All Branches - MTD
2022-2-All Branches - MTD	2022	2	8	All Branches - MTD
2022-3-All Branches - MTD	2022	3	13	All Branches - MTD
2022-4-All Branches - MTD	2022	4	17	All Branches - MTD
2022-5-All Branches - MTD	2022	5	21	All Branches - MTD
2022-6-All Branches - MTD	2022	6	26	All Branches - MTD
2022-7-All Branches - MTD	2022	7	30	All Branches - MTD
2022-8-All Branches - MTD	2022	8	34	All Branches - MTD
2022-9-All Branches - MTD	2022	9	39	All Branches - MTD
2022-1-SC Branches - YTD	2022	1	4	SC Branches - YTD
2022-2-SC Branches - YTD	2022	2	8	SC Branches - YTD
2022-3-SC Branches - YTD	2022	3	13	SC Branches - YTD
2022-4-SC Branches - YTD	2022	4	17	SC Branches - YTD
2022-5-SC Branches - YTD	2022	5	21	SC Branches - YTD
2022-6-SC Branches - YTD	2022	6	26	SC Branches - YTD
2022-7-SC Branches - YTD	2022	7	30	SC Branches - YTD
2022-8-SC Branches - YTD	2022	8	34	SC Branches - YTD
2022-9-SC Branches - YTD	2022	9	39	SC Branches - YTD
2022-1-SC Branches - MTD	2022	1	4	SC Branches - MTD	3 358
2022-2-SC Branches - MTD	2022	2	8	SC Branches - MTD	3 049
2022-3-SC Branches - MTD	2022	3	13	SC Branches - MTD	3 308
2022-4-SC Branches - MTD	2022	4	17	SC Branches - MTD	3 018
2022-5-SC Branches - MTD	2022	5	21	SC Branches - MTD	3 516
2022-6-SC Branches - MTD	2022	6	26	SC Branches - MTD	5 445
2022-7-SC Branches - MTD	2022	7	30	SC Branches - MTD	4 053
2022-8-SC Branches - MTD	2022	8	34	SC Branches - MTD	4 077
2022-9-SC Branches - MTD	2022	9	39	SC Branches - MTD	4 283
2022-10-All Branches - YTD	2022	10	42	All Branches - YTD	382 559	28518
2022-10-All Branches - MTD	2022	10	42	All Branches - MTD
2022-10-SC Branches - YTD	2022	10	42	SC Branches - YTD
2022-10-SC Branches - MTD	2022	10	42	SC Branches - MTD	2 548
2022-10-All Branches - YTD	2022	10	43	All Branches - YTD	391 349	37308
2022-10-All Branches - MTD	2022	10	43	All Branches - MTD
2022-10-SC Branches - YTD	2022	10	43	SC Branches - YTD
2022-10-SC Branches - MTD	2022	10	43	SC Branches - MTD	3 344
2022-11-All Branches - YTD	2022	11	44	All Branches - YTD
2022-11-All Branches - MTD	2022	11	44	All Branches - MTD
2022-11-SC Branches - YTD	2022	11	44	SC Branches - YTD
2022-11-SC Branches - MTD	2022	11	44	SC Branches - MTD

Re: Calculate difference between current and previous row with a condition

Jihwan_Kim — Tue, 15 Nov 2022 04:23:24 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.

Diff CC = VAR _previous = MAXX ( FILTER ( Data, Data[Fiscal Year] = EARLIER ( Data[Fiscal Year] ) && Data[Fiscal Month] = EARLIER ( Data[Fiscal Month] ) - 1 && Data[Agregation Type] = "All Branches - YTD" ), Data[Applicants] ) RETURN IF ( Data[Agregation Type] = "All Branches - YTD" && NOT ISBLANK ( Data[Applicants] ) && NOT ISBLANK ( _previous ), Data[Applicants] - _previous )

Re: Calculate difference between current and previous row with a condition

v-yinliw-msft — Tue, 15 Nov 2022 07:38:28 GMT

Hi Sultanista ,

You can try this method:

New column:

Diff = VAR _curr = CALCULATE ( SUM ( 'Table'[Applicants] ), 'Table'[Agregation Type] = "All Branches - YTD" ) RETURN IF ( [Fiscal Month] = 1 || 'Table'[Agregation Type] <> "All Branches - YTD" || ISBLANK ( 'Table'[Applicants] ), BLANK (), _curr - CALCULATE ( SUM ( 'Table'[Applicants] ), FILTER ( 'Table', [Agregation Type] = "All Branches - YTD" && [Fiscal Year] = EARLIER ( 'Table'[Fiscal Year] ) && [Fiscal Month] = EARLIER ( 'Table'[Fiscal Month] ) - 1 ) ) )

The result is:

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate difference between current and previous row with a condition

Sultanista — Wed, 16 Nov 2022 08:30:48 GMT

It works great! Is it possible to move those results from current place to the corresponding fiscal year and month but aggregation type = All Branches - MTD ?

And having the 1st value of the year (month =1) same as in YTD?

topic Re: Calculate difference between current and previous row with a condition in DAX Commands and Tips

Calculate difference between current and previous row with a condition

Re: Calculate difference between current and previous row with a condition

Re: Calculate difference between current and previous row with a condition

Re: Calculate difference between current and previous row with a condition