Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
here is a workaround for you
pls see the attachment below
Proud to be a Super User!
@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
Hi @joshuac055 ,
I update my sample pbix file(see the attachment), please check if that is what you want.
Total LBs difference =
VAR _minweek =
CALCULATE(MIN ( 'Table'[Date] ),ALLSELECTED('Table'))
VAR _maxweek =
CALCULATE(MAX ( 'Table'[Date] ),ALLSELECTED('Table'))
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
Total $'s difference =
VAR _minweek =
CALCULATE(MIN ( 'Table'[Date] ),ALLSELECTED('Table'))
VAR _maxweek =
CALCULATE(MAX ( 'Table'[Date] ),ALLSELECTED('Table'))
VAR _loc =
SELECTEDVALUE ( 'Table'[Location] )
VAR _product =
SELECTEDVALUE ( 'Table'[Product] )
VAR _mints =
CALCULATE (
max ( 'Table'[Total $'s] ),
FILTER ( 'Table', 'Table'[Date] = _minweek )
)
VAR _maxts =
CALCULATE (
SUM ( 'Table'[Total $'s] ),
FILTER ( 'Table', 'Table'[Date] = _maxweek )
)
RETURN _maxts-_mints
Best Regards
here is a workaround for you
pls see the attachment below
Proud to be a Super User!
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.
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?
I don't have other file. Do you have another laptop that can install the new version?
Proud to be a Super User!
I will try on my PC at home later today.
@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
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.
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
Here is an example of what I mean:
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 | B | 15 | $ 112.00 |
4/13/2024 | 3 | C | 12 | $ 96.00 |
If I were to compare week 4/27 and 4/13, notice that location 2 product A has no data that week. However, I would still want it to return this result when comparing 4/27 to 4/13:
Location | Product | Total LBs difference | Total $'s difference |
1 | A | -4 | -10 |
2 | A | 9 | 95 |
2 | B | -4 | -10 |
3 | C | -4 | -10 |
Hi @joshuac055 ,
I update my sample pbix file(see the attachment), please check if that is what you want.
Total LBs difference =
VAR _minweek =
CALCULATE(MIN ( 'Table'[Date] ),ALLSELECTED('Table'))
VAR _maxweek =
CALCULATE(MAX ( 'Table'[Date] ),ALLSELECTED('Table'))
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
Total $'s difference =
VAR _minweek =
CALCULATE(MIN ( 'Table'[Date] ),ALLSELECTED('Table'))
VAR _maxweek =
CALCULATE(MAX ( 'Table'[Date] ),ALLSELECTED('Table'))
VAR _loc =
SELECTEDVALUE ( 'Table'[Location] )
VAR _product =
SELECTEDVALUE ( 'Table'[Product] )
VAR _mints =
CALCULATE (
max ( 'Table'[Total $'s] ),
FILTER ( 'Table', 'Table'[Date] = _minweek )
)
VAR _maxts =
CALCULATE (
SUM ( 'Table'[Total $'s] ),
FILTER ( 'Table', 'Table'[Date] = _maxweek )
)
RETURN _maxts-_mints
Best Regards
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |