Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rafterse
Helper I
Helper I

How do i get the details of the delta between weeks

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. 

 

WeekBegining30/6/2407/07/204
 Week1Week2
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 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @rafterse ,

@Ashish_Mathur Thanks for your concern about this case!

And @rafterse , here is my sample data:

vjunyantmsft_0-1720060586101.png

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:

vjunyantmsft_1-1720060723638.png

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

vjunyantmsft_2-1720060777674.png

vjunyantmsft_3-1720060802290.png

And the final output is as below:

vjunyantmsft_4-1720060832496.png


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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @rafterse ,

@Ashish_Mathur Thanks for your concern about this case!

And @rafterse , here is my sample data:

vjunyantmsft_0-1720060586101.png

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:

vjunyantmsft_1-1720060723638.png

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

vjunyantmsft_2-1720060777674.png

vjunyantmsft_3-1720060802290.png

And the final output is as below:

vjunyantmsft_4-1720060832496.png


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.

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.