Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone.
I've been struggling for a while with the following problem:
I have the snapshots of revenue data (with different items
) for different dates. I want to compare difference only between two dates, so I filter out all the others using filter visual. Next, I create a measures which capture those two selected dates using MAX and MIN. Afterwards, I use those measures to sum up values for the MAX date and MIN date and find the difference.
Everything works perfectly except for the case when the item was in one date, but not in the other. The problem occurs becuase for such items there is one date only for both MIN and MAX, and I get the same values for the dates and 0 as a difference, instead of one date being zero and the other with the value. The probem occurs becuase of filtering for each item in the table.
That is why my question is: Is there any way to calculate those MAX and MIN numbers from filter and make them constants for table calculations? (snapshot below - Minimum should always be 10/8/2018 and maximum should always be 10/15/2018)
Thanks in advance!
Solved! Go to Solution.
Hi Sam,
Thanks, I've already found the solution myself. Made it using the variables.
Regards,
Yassaui
Can you explain a bit more how you solved using variables?
Maybe by using a Measure or perhaps a VAR in your calculation, but can't quite wrap my head around what you are doing. Does the information displayed respresent source data or output data? Can you provide sample source data in text? Can you provide your calculations? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Greg!
I'll try to explain on simple example. Suppose we have this source Data:
Date Item Quantity
1/1/2018 Apple 1
1/1/2018 Apple 2
1/1/2018 Apple 1
2/1/2018 Banana 5
2/1/2018 Apple 3
2/1/2018 Banana 1
2/1/2018 Apple 4
3/1/2018 Banana 3
There are three dates: 1/1/2018, 2/1/2018, and 3/1/2018. Suppose I want to compare iteam for the first two dates.
I create visual: filter with dates only 1/1/2018 and 2/1/2018. And I want to understand which dates out of three were selected. I create two measures:
Maximum = MAX(Data[Date]) and Minimum = MIN(Data[Date])
So, because I have filter set up, these two measures if used should show Maximum = 2/1/2018 and Minimum = 1/1/2018.
Next I want to calculate the quantity of items for each date:
Maxmonth = CALCULATE(IF(Data[Date]=[Maximum],SUM(Data[Quantity]),BLANK())) - do not have access to PBI now, the logic should be something similar to this
Minmonth = CALCULATE(IF(Data[Date]=[Minimum],SUM(Data[Quantity]),BLANK()))
ALso, I want to see difference: Difference = [Maxmonth] - [Minmonth]
Now I want to create the comparison table visual (pivot) with the following columns:
Item | First Date | Second Date | Difference
If I add everything to table, here is what I get for Apples:
Item | Minmonth (1/1/2018) | Maxmonth (2/1/2018) | Difference
Apple 4 7 -3
For apples everything is as expected, no problems. However, here is what will be with bananas:
Item | Minmonth (1/1/2018) | Maxmonth (2/1/2018) | Difference
Banana 6 6 0
But expected values are:
Item | Minmonth (1/1/2018) | Maxmonth (2/1/2018) | Difference
Banana 6 6
The problem is, when it comes to bananas, it filters out everything for bananas, and in the Date column we have only one date left, which is 2/1/2018. Therefore, it is both Maximum and Minimum value for bananas. - this is the problem
I do not want it to work like that. I want to calculate Maximum and Minimum, and somehow make them constant, so that they are not affected by items filtering. Or probably there is another simple way to solve this problem?
(I know that I can create in the source table another column withe the first occurence date of the item in the table. However, the problem is that the formula takes looooots of time to be calculated, because I have about 300k+ rows. I need something simpler, with less calculations)
Hope this explanation helps to understand what I need. Thanks!
@Anonymous,
You may add ALLSELECTED Function.
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/
Hi Sam,
Thanks, I've already found the solution myself. Made it using the variables.
Regards,
Yassaui
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |