Can i Sum the value in a set of data based on the max date selected in the date filter

Sara321

Hi

I have a set of data which is a snap shot of records each month. I want to Sum one column only where the date is equal to the latest date selected using the date filter in the chart. I tried the formula below but it is giving me no output.

SUM(CASE WHEN `ACV Fiscal Month`= MAX(`ACV Fiscal Month`) then `ARR Amount(USD)`else 0 end)

The Max formula works on it's own but it doesn't seem to like being compared to in the when statement..

Many Thanks in advance for any help!

GrantSmith

Hi @Sara321

You can't aggregate an aggregate. You'd need to use a dataflow to pre-aggregate your data to calculate your max month. The downside to this is that it's not affected by any filtering taking place on your card.

Sara321

Thanks @GrantSmith I had a feeling you would say that...