I've been trying all sorts of past solutions from the forums but nothing has solved my specific issue yet. The goal is to pull the most recent number for each "account" and to show to date that number is from. The problem is that the accounts will have different dates.
In the attached workbook, the goal would be to show a table like below-
ClientAccount
YearMonthAssetsClient 1Client 1 - Fund A2019November175Client 2Client 2 - Fund B2019July300Client 3Client 3 - Fund C2019November1075Client 4Client 4 - Fund D2019April300Client 5Client 5- Fund E2019November2600Client 5 - Fund F2019November5400
So while in this case it would consistently be November 2019 as the most recent, a mix would also be from earlier months, or could potentially be past years. Additionally, sometimes 0s will populate after the most recent asset number, which would need to be ignored.
I'd tried some of the ideas in this post- Get last non-null value but haven't been able to quite get there. Any help on filters/calculations would be greatly appreciated.
Hi Brendan,
Using LODs:
Two calculations:
{fixed [Client], [Account]: MAX(IF ZN([Net Assets])<>0 THEN [Date] END)}
{fixed [Client], [Account], [Date]: MAX([Date]=[Account - Max Date])}
Using Table Calculations (so it works in this table view):
One calculation:
MAX([Date])=WINDOW_MAX(IF ZN(SUM([Net Assets]))>0 THEN MAX([Date]) END)
Compute using Date.
Best,
Bryce | @BLarsenViz
