Forum Discussion

BlakeNol's avatar
BlakeNol
Frequent Visitor
9 years ago
Solved

Max Date with filter / selection

Hi Everyone,

 

I think this should be easy. The data is pretty simple, it is a table with EntDate (the day the data was entered) and Volume (the volume recorded on that day). I would like to have a card that displays the volume on the last day that is selected via slicer. I would like the other card to display the average volume of the period selected.

 

I can get this to work without a slicer, but once I narrow the date range via slicer the cards do not work.

 

I created 2 custom date mesauses

MaxDate = CALCULATE(Max(PrdQuery[EntDate]),All(PrdQuery))

This always returns the last date in the data set

 

MaxDateSelection = LASTDATE(PrdQuery[EntDate])

This returns the entry date in the data set based on the filter in the slicer. I want this measure to return the last date in the selection, similar to how MaxDate returns the last date in the entire data set.

 

I can get this to work to always return the value for the last day in the dataset by creating another measure that compares MaxDate to EntDate and filter the visual off this measure. I would like to use the same strategy for the last filtered/selected date.Screenshot

Need Help

3 Replies

    • BlakeNol's avatar
      BlakeNol
      Frequent Visitor
      9 years ago

      Thanks! I was pretty sure I tried this yesterday, but I guess not. 

       

      For clarity, the formula is:

       

      MaxDateAllSelected = CALCULATE(Max(PrdQuery[EntDate]),ALLSELECTED(PrdQuery))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Ive had a similar issue without any sorting or linked table issues. Im using an ALL filter in PBI to return the last yearPeriod in my data 202208. CALCULATE(max(Data[YearMonth]),all(Data[YearMonth])). Its fine until my data filtering (slicers) excludes records with that date then it refers to the max data available in my case 201908. In my eyes ALL should mean ALL. In the end, in my Transform layer I created a reference to my main data as an orphan table then took the max of the Year Month as a single record. I then used the following formula. CALCULATE(max(CM[YearMonth])) where CM is my orphan record. Again ALL should mean ALL and it doesnt inspire confidence when something as fundemental as this doesnt work properly.