Trying to return the maximum value of a filtered date column in Power BI

1
votes

I have a table within Power BI that has a date field, and a value field. I am filtering on this date field, using a slicer, to sum all of the value data before the specified date. I would like to get this date value to use in a LOOKUPVALUE() elsewhere (to get a conversion rate).

Is there a way to accomplish this?

I have tried the DAX functions that return the values of a particular table/column with filters preserved but this never seems to work, and just returns the entire dataset, e.g. VALUES(), FILTERS(), ALLEXCEPT().

Any help would be greatly appreciated!

1
filterpowerbidaxslicers
What's the context of "elsewhere"? A Measure? A Calculated Column on the same table? – Mike Honey
I need to retrieve a value from another table, so using the LOOKUPVALUE() function and the max date value, so a calculated column on the same table I guess. – Alex Kitson
Calculated column results are stored during the Refresh process. They cannot respond to slicers. You probably need to create Measures to achieve what you want. – Mike Honey

1 Answers

4
votes

I found a solution using measures.

The DAX for future reference: 

Filter Date = CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))