topic Re: How do i get the details of the delta between weeks in DAX Commands and Tips

How do i get the details of the delta between weeks

rafterse — Fri, 05 Jul 2024 00:49:01 GMT

Hi,

i want to get the detail of changes between weeks .

two weeks of data is on one query

I want to report on only the changes in purchases from week to week and get the detail of the extra purchase.

WeekBegining	30/6/24	07/07/204
	Week1	Week2
Customer A	Product A	Product A
	Product B	Product B
	Product C	Product C
	Product D	Product D
		Product E
Customer B	Product A	Product A
Customer C	Product A	Product B
	Product B

Result should look like this

	Change	Details
Customer A	+1	Product E
Customer C	-1	Product A

thanks in advance

Re: How do i get the details of the delta between weeks

Ashish_Mathur — Thu, 04 Jul 2024 00:27:15 GMT

Hi,

Is there a date column? If yes, then share that column as well.

Re: How do i get the details of the delta between weeks

Anonymous — Thu, 04 Jul 2024 02:41:05 GMT

Hi rafterse ,

Ashish_Mathur Thanks for your concern about this case!

And rafterse , here is my sample data:

Use this DAX to create a measure to calculate the change:

Change = VAR _1 = CALCULATE( DISTINCTCOUNT('Table'[Week1]), ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Week1] <> BLANK() ) VAR _2 = CALCULATE( DISTINCTCOUNT('Table'[Week2]), ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Week2] <> BLANK() ) RETURN _2 - _1

And use this DAX to create another measure to filter the detail columns:

Measure 2 = IF( (MAX('Table'[Week1]) <> BLANK() && MAX('Table'[Week2]) = BLANK()) || (MAX('Table'[Week1]) = BLANK() && MAX('Table'[Week2]) <> BLANK()), 1, 0 )

Put the column Customer, Week1, Week2 and the measure Change into the table visual:

And put the Measure 2 into the Filters on this visual:

And the final output is as below:

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