Rolling up the Last Value
I am building a chart where the user can select multiple metrics and the scales adjust accordingly on each side. Currently, the X-axis shows the period which can be shown by Month, QTR, and Year based on the users preference. A problem I'm into though is some of the metrics are balance metrics and therefore when they get rolled up it needs to take the last metric available for the new period selection. For example when going from Month to Quarter, Q4 would need to take the latest value. One nuance is that for months that we don't have information it shows a zero which is then pulled for Q4 instead of the most recent month. Is there a formula/way to overcome this?
I really appreciate the help!
It seems that your data set has some rows with empty values for November and December. Otherwise those month should not show up in you visualization. If you filter out the empty values it should work fine.