Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have searched and searched through these questions and I can't find an answer that works for me. I have a table with multiple columns like so:
Date | Feb FC | Mar FC |
1-Jan | 35 | 33 |
2-Jan | 37 | 34 |
3-Jan | 38 | 35 |
4-Jan | 40 | 36 |
5-Jan | 42 | 37 |
6-Jan | 37 | 38 |
7-Jan | 34 | 37 |
8-Jan | 43 | 40 |
9-Jan | 41 | |
10-Jan | 42 | |
11-Jan | 40 | |
12-Jan | 44 | |
13-Jan | ||
14-Jan | ||
15-Jan |
I want to return the last value that is not null in the column and use it in a chart. I tried bottom() and looked into firstsortedvalue() to achieve this but I was unable to come up with a solution. Assuming this data will change daily (as more values are added) how would I find the last value in the column and return it as the Month to date value (ie JanMTD would be 43). From this data set I would then need to create table with a Month column and the corresponding MTD value like so:
FC | Feb | Mar |
---|---|---|
Forecast | 43 | 44 |
Many thanks!!
May be this:
FirstSortedValu({<[Feb FC] = {"=Len(Trim([Feb FC])) > 0"}>}[Feb FC], -Date)
FirstSortedValu({<[Mar FC] = {"=Len(Trim([Mar FC])) > 0"}>}[Mar FC], -Date)