Slicer to affect dynamic filter - if max date less than selected value

[RobCook]

Hi all,

I'm working with a data set that stored total sales each day, rather than delta. Something I'm working on shifting away from.

I've written a query to calculate the daily movement, but I'm having issues building a visualisation to show the total sales for all products with a date filter/slicer. That's because when products stop being sold they stop being included on the report, but I need them to remain on the visual.

Example below:

StoreId	ProductId	SaleDate	Units	Value
1	1	15/02/2023	136	612
1	1	14/02/2023	123	553.5
1	1	13/02/2023	120	540
1	2	14/02/2023	54	270
1	2	13/02/2023	48	240
1	3	15/02/2023	520	2080
1	3	14/02/2023	499	1996
1	3	13/02/2023	472	1888

Product 2 stops being sold on 14/02/2023 so isn't recorded on 15/02/2023, but if the Slicer selects 15/02/2023 I still need product 2 totals to show.

I need a way to have a slicer to select the Sale Date conditionally. If the Max(Sale Date) < SelectedValue then give most recent Sale Date data. Or similar.

Apologies if that's poorly explained, it's a very specific scenario/product which is difficult to give examples.

[v-zhangti · Accepted solution]

Hi, @RobCook 

You can try the following methods.
Table:

Date = CALENDAR(DATE(2023,2,1),DATE(2023,2,28))

Measure:

Measure = 
Var _maxdate=CALCULATE(MAX('Table'[SaleDate]),ALLEXCEPT('Table','Table'[ProductId]))
Var _total=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[ProductId]=SELECTEDVALUE('Table'[ProductId])&&[SaleDate]<=SELECTEDVALUE('Date'[Date])))
Return
IF(SELECTEDVALUE('Date'[Date])>=_maxdate,_total)

Is this the result you expect? If not, please give examples of your desired output?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.