I have the following DAX that aims to bring back the date where the highest running total was achieved. i.e. the date 09/09/22 would show because it had the highest running total, say 1000. Every day since then the running total has reduced but the above date would remain because it has the highest running total.
Today the running total has now exceded 1000 (1012) and I was expeting to get the latest date i.e. 17/02/2023 but I get an error. I have no filters applied on the page what am I missing? See DAX and error message below:
Max Date of Running =
Var __SelectedMax =
CALCULATE(
MAX( DSET_DAILY_METRICS[Running Total]),
ALLSELECTED(DSET_DAILY_METRICS)
)
RETURN
CALCULATE(
VALUES(DSET_DAILY_METRICS[Date]),
FILTER(
ALL(DSET_DAILY_METRICS[Date]),
MAX( DSET_DAILY_METRICS[Running Total]) = __SelectedMax )
)
Error Message:
MdxScript(Model) (29, 5) Calculation error in measure 'DSET_DAILY_METRICS'[Max Date of Running]: A table of multiple values was supplied where a single value was expected.
