Forum Discussion
Saving Selected dates from a visual filter
Hi all,
I would appreciate help with the following issue (sorry in advance that I cannot upload an example due to sensentivity of the topic).
For exposition lets consider the following table
| Date | ID | Value |
| 31/01/2022 | 1 | 10 |
| 31/01/2022 | 2 | 11 |
| 1/10/2022 | 1 | 6 |
| 1/10/2022 | 1 | 13 |
As you can notice, between the 31/01/2022 and 1/10/2022, ID=2 no longer exists (due to legitimate reasons). And on Date 1/10/2022 we have two entries with ID now (also legitimate).
I am working on computing the change in values per ID for any two given dates selected by the user (which exists in the date column).
To do this I calculate two measures which take the minimum and maximum value of the selected dates by the user:
date_upper = CALCULATE(MAX(TABLE[DATES]), ALLSELECTED(TABLE[DATES])
date_lower = CALCULATE(MIN(TABLE[DATES]), ALLSELECTED(TABLE[DATES])
Then create a measure to compute the changes:
changes = CALCULATE(SUM(TABLE[Value], TABLE[Date]= date_upper)) - CALCULATE(SUM(TABLE[Value], TABLE[Date]= date_lower))
I then go ahead to create a stacked bar graph on the changes per ID. And for completeness lets say the user has selected using the filter on the vidual the dates above (31/01/2022, 01/10/2022)
The problem is that for ID =2, the date_lower and date_upper are the same because this ID does not exist on date 01/10/2022. Hnece the result is 0. However, I would like the upper date for this ID to be 01/10/2022 and get a change of -10 (=0-10).
How would I go about doing this?
I tried to save the dates the user selected and check if for this date the ID is present but I cannot find a way to save the dates statically after the user filters them
1 Reply
sozinscomet , You need to use a separate date table to make that work
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] =_max)) - calculate( sum(Table[Value]), filter('Date', 'Date'[Date] =_min))Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
