Create a Table that compares two different weeks of data stored in the same table

[joshuac055]

Hello, I am trying to create a table that would compare two different weeks of data that are currently stored in the same table.

Here's an example data set: 

Date	Location	Product	Total LBs	Total $'s
4/27/2024	1	A	10	$ 100.00
4/27/2024	2	A	9	$   95.00
4/27/2024	2	B	11	$ 102.00
4/27/2024	3	C	8	$   86.00
4/20/2024	1	A	12	$ 105.00
4/20/2024	2	A	11	$ 100.00
4/20/2024	2	B	13	$ 107.00
4/20/2024	3	C	10	$   91.00
4/13/2024	1	A	14	$ 110.00
4/13/2024	2	A	13	$ 105.00
4/13/2024	2	B	15	$ 112.00
4/13/2024	3	C	12	$   96.00

I want to be able to then create a table or visual that would compare the total lbs and $s for each location and product. Lets say for example I wanted to compare 4/27 with 4/13 it would look like this:

Location	Product	Total LBs difference	Total $'s difference
1	A	-4	-10
2	A	-4	-10
2	B	-4	-10
3	C	-4	-10

Ideally, the visual I create on my report would have a slicer that lets you choose the two weeks you are comparing, but I do not know if that is even possible. If that is not possible, I would like to start with just comparing two weeks and that table can be static. Would appreciate any help. Thanks!

[ryan_mayu · Accepted solution]

@joshuac055 

here is a workaround for you

pls see the attachment below

[ryan_mayu · Accepted solution]

@joshuac055 

here is a workaround for you

pls see the attachment below

[joshuac055]

Hello, I was able to test this this morning. However, I get an error with the forumula in regards to the Max function. In error, I had my test data using numerial values for the location when in reality it is a name. I think I will need to adjust the formula for this. 

[joshuac055]

It won't let me open the file because it's on a newer version of the PowerBI app. It takes my org forever to reply to a request to update apps. Do you have another file I could use?

[ryan_mayu]

I don't have other file. Do you have another laptop that can install the new version?

[joshuac055]

I will try on my PC at home later today.

[v-yiruan-msft · Accepted solution]

@ryan_mayu Thanks for your contribution on this thread.

Hi @joshuac055 ,

@ryan_mayu provided you a pbix file with the solution, did you check that? If it can help you get the expected result? If yes, you can mark his post as the solution. Thank you.

Otherwise, you can create two measures as below to get it. Please find the details in the attachment.

Total $'s difference = 
VAR _minweek =
    MIN ( 'Table'[Date] )
VAR _maxweek =
    MAX ( 'Table'[Date] )
VAR _mints =
    CALCULATE (
        SUM ( 'Table'[Total $'s] ),
        FILTER ( 'Table', 'Table'[Date] = _minweek )
    )
VAR _maxts =
    CALCULATE (
        SUM ( 'Table'[Total $'s] ),
        FILTER ( 'Table', 'Table'[Date] = _maxweek )
    )
RETURN
    _maxts - _mints

Total LBs difference = 
VAR _minweek =
    MIN ( 'Table'[Date] )
VAR _maxweek =
    MAX ( 'Table'[Date] )
VAR _minLB =
    CALCULATE (
        SUM ( 'Table'[Total LBs] ),
        FILTER ( 'Table', 'Table'[Date] = _minweek )
    )
VAR _maxLB =
    CALCULATE (
        SUM ( 'Table'[Total LBs] ),
        FILTER ( 'Table', 'Table'[Date] = _maxweek )
    )
RETURN
    _maxLB - _minLB

Best Regards

[joshuac055]

This works except for one small thing. I did not mention that one week a location could have one product and a few weeks later it could have two products. I would like it to be able to compare those two weeks by product even if one week is missing a product. Let me know if that does not make sense.

[v-yiruan-msft]

Hi @joshuac055 ,

I'm not clear about your above requirement, could you please provide some examples to explain the calculation logic and final expected result? Thank you.

one week a location could have one product and a few weeks later it could have two products. I would like it to be able to compare those two weeks by product even if one week is missing a product. 

---

Best Regards