Storing calculated value as constant

[Anonymous]

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!

[Joy2u]

Can you explain a bit more how you solved using variables?

[Greg_Deckler]

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

[Anonymous]

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!

[v-chuncz-msft]

@Anonymous,

You may add ALLSELECTED Function.

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

[Anonymous · Accepted solution]

Hi Sam,

Thanks, I've already found the solution myself. Made it using the variables.

Regards,

Yassaui