Static dataset filters based on max date

[Anonymous]

Hi all,

I have a dataset that looks like the following:

Date, Project ID, Balance, Funded Amt

11/12/18, 123, 500, 2000

11/12/18, 234, 1000, 3000

11/12/18, 235, 700, 5000

11/13/18, 123, 450, 2000

11/13/18, 234, 800, 3000

11/13/18, 235, 700, 5000

11/14/18, 123, 400, 2000

11/14/18, 234, 700, 3000

11/14/18, 235, 500, 5000

I would like to create a visual that aggregates the data from projects 234 and 235 and shows the aggregated current balance (as of max date) against aggregated total funded. I want this as a static display that is not modified by selections on the dashboard. So the 11/14/18 chart would look like:  $1,200 balance vs. $8,000 funded.

Step one is to get total funded. This is my formula that is not working:

sum({1<Date = {"max(Date)"}, [Project Id] = {"2*"}>} [Funded Amt])

However, when I do the following, formula, it works:

sum({1<Date = {"11/14/18"}, [Project Id] = {"2*"}>} [Funded Amt])

Obviously, step 2 would look similar, replacing [Funded Amt] with [Balance], but for some reason I can't get the date to resolve properly.

I tried to remove the "" around "max(Date)", but I get the following error: Error in set modifier ad hoc element list: ',' or ')' expected"

Any help would be appreciated!

[sunny_talwar · Accepted solution]

Try this

Sum({1<Date = {"$(=Max(Date))"}, [Project Id] = {"2*"}>} [Funded Amt])

or

sum({1<Date = {"$(=Max({1} Date))"}, [Project Id] = {"2*"}>} [Funded Amt])

[Anonymous]

That did it, thank you!

Functionally, what is the $ operator doing? Is it casting Date as a string?

[sunny_talwar]

Look here to read about

The Magic of Dollar Expansions

[sunny_talwar]

Also look here

A Primer on Set Analysis