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

Sara321 · 2022-06-16T10:09:19+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

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.

All comments

GrantSmith

Hi @Sara321

Sara321

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