Skip to main content

Hi Folks, struggling with an issue here. I have HC by dept over time. What I’m trying to do is take the last month of actuals, in this case December. In the example below, its 805.

 

However, when I remove lastnonblank month, the amount jumps to 917.

 

What I noticed when I drill into the 917 is that while month is taking the last month, the other dimensions are being flattened so counts are duplicating where things have changed over time. 



How do I take the lastnonblank of the other dimensions as well?

​@julian_s Can you try this formula:

'Metric Source'cMonth = IFDEFINED('Metric Source'cremove: all other dimensions except Month],Month)tremove lastnonblank:Month]]

At first I couldn’t get it to work. I was able to develop a work around. I embedded a variable then set the variable to equal the switch over date. This might actually work better for us for now so we have some intentionality around when we roll actuals forward.

But I must have keyed in something wrong. This worked perfectly! 
 

Could you explain why this works? I suppose I don’t quite understand how ISDEFINED works to select the right data.

​@julian_s  you can understand ifdefined as Excel equivalent: IF(NOT(ISBLANK(cell)), value if true, value if false).

In our case, we want the formula to return Month if the metric is not blank in that month.
 

 

Then we can get the last Month using remove lastnonblank: Month.
Aug24 is the last month for filtering the original metric.

 

Awesome thanks!

Reply


Ask your question here!

Terms & ConditionsCookie settings