Forum Discussion

Adam15's avatar
Adam15
Frequent Visitor
2 years ago
Solved

SelectedValue and Dates

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?
  • timalbers's avatar
    timalbers
    2 years ago

    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.

4 Replies

  • timalbers's avatar
    timalbers
    Skilled Sharer
    2 years ago

    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

    • Adam15's avatar
      Adam15
      Frequent Visitor
      2 years ago

      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.
  • timalbers's avatar
    timalbers
    Skilled Sharer
    2 years ago

    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.

    • Adam15's avatar
      Adam15
      Frequent Visitor
      2 years ago

      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.