topic SelectedValue and Dates in DAX Commands and Tips

SelectedValue and Dates

Adam15 — Tue, 05 Mar 2024 08:00:00 GMT

Hello,

I'm trying to make a report that shows the stock size, prize and total value of different items. I need to tell the total value using different approaches: Value when bought, Value of the average last 6 months selling price, Value on the last selling price. And I need to use a slicer to change the date I'm checking.

When I'm using this measure, it can show the current value on last selling price on febr 29:

Last Selling Value = calculate(SUMX(SUMMARIZE(Invoice,Product[Name],Depot[Name]),[Last Selling Price]*Calculate(SUM(Depot[Ammount]), 'Date'[Date]=DATE(2024,02,29)),All('Date'[Date]))

But when I'm trying to change the DATE(2024,02,29) to SELECTEDVALUE('Date'[Date]) it shows BLANk. On the slicer I can choose only a value, not multiple.

I have tried to rewrite the measure using MIN, MAX, LASTDATE but I had no success yet. What can I do?

Re: SelectedValue and Dates

timalbers — Tue, 05 Mar 2024 08:25:44 GMT

Hi Adam15 ,

based on the information you gave, here is some advise:

Make sure you are using the exact same Date field in your slicer as you use for SELECTEDVALUE
Make sure the relationship between your date table and the fact table is set up correctly (maybe share here)
Also it is important how you set up the Last Selling Price measure (maybe you could share this one's DAX as well here)

Hope this helped you in some way already

Cheers

Tim

Re: SelectedValue and Dates

Adam15 — Tue, 05 Mar 2024 09:38:04 GMT

The first two is good, I can't really show picture, because the tables are on my native language, I just translated the columns when I asked.

This is the measure for the last selling price:

Last Selling Price = Calculate(max(Invoice[Price]), all('Date'[Date]))

I work in an industry where the last selling price is equal to the maximum.

Re: SelectedValue and Dates

timalbers — Tue, 05 Mar 2024 10:46:18 GMT

Could you just leave out the ALL( 'Date'[Date] ) of the "Last Selling Value" measure.
This is an explicit filter context which prevents the slicer filter from filtering your dates.

Re: SelectedValue and Dates

Adam15 — Tue, 05 Mar 2024 13:33:53 GMT

It works for every product we sold that day, but for those we haven't it gives blank back.

edit: I got it, I just had to replace the Invoice to Depot in the Summarize.