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!
