Skip to main content
Solved

WF Planning: Remove Lastnonblank month Gives A Different Return

  • January 30, 2025
  • 4 replies
  • 132 views
julian_s
Apprentice Author
Forum|alt.badge.img

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?

Best answer by Min Li

​@julian_s Can you try this formula:

'Metric Source'[Month = IFDEFINED('Metric Source'[remove: all other dimensions except Month],Month)[remove lastnonblank:Month]]

4 replies

Min Li
Master Helper
Forum|alt.badge.img+12
  • Min LiMaster Helper
  • Master Helper
  • 71 replies
  • Answer
  • January 31, 2025

​@julian_s Can you try this formula:

'Metric Source'[Month = IFDEFINED('Metric Source'[remove: all other dimensions except Month],Month)[remove lastnonblank:Month]]

Min Li
julian_s
Apprentice Author
Forum|alt.badge.img
  • julian_sApprentice Author
  • Author
  • Apprentice Author
  • 4 replies
  • February 7, 2025

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.

Min Li
Master Helper
Forum|alt.badge.img+12
  • Min LiMaster Helper
  • Master Helper
  • 71 replies
  • February 7, 2025

​@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.

 

Min Li
julian_s
Apprentice Author
Forum|alt.badge.img
  • julian_sApprentice Author
  • Author
  • Apprentice Author
  • 4 replies
  • February 18, 2025

Awesome thanks!


Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement