Saving Selected dates from a visual filter

[sozinscomet]

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

[amitchandak]

@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-bd52912a5b...
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.