how to pull the amount for the last non-blank month only

Hello @MKSMPB ,

If I understand it correctly, you are pulling data from Metric 1 for an specific item of the ‘Cloud Cost From Marketing Team’ list. Since that data is the monthly extract, you are only interested in the latest month.

The question you need to ask yourself is what is the meaning of the latest month. When using LASTNONBLANK, Pigment is returning the data of the latest month that was not blank. However, that could be our current month (Mar 24), but it could also be the data from Dec 23 for a specific item if they have not had any recent costs.

Would that be what you want? If not, could you please let us know exactly what Month are you after?

Cheers!

Hey Bruno,

I am looking to pull through the latest month of data rather than the current month.
In the data currently, that would be Jan 2024.

When i have tried to use lastnonblank it seems to have no effect on the result. Currently, just pulling through all months in the source data.

Thanks!

Matthew

Hello @MKSMPB ,

Perfect, thank you very much for the clarification. As such, I understand you have the following:

Metric 1 = 'Cloud Cost Input From Marketing'

Metric 2 = 'Cloud Cost Input From Marketing'[by constant: 'Cloud Cost From Marketing Team'."Staging"]

Metric 3 = Metric 2[BY LASTNONBLANK: Month]

I suspect that is what you have tried. Have you removed Month as a dimension from Metric 3 since it is no longer needed?

Many thanks,

Bruno

Removing month doesn’t seem to work as currently the above formula pulls through all available months of data (Dec 23 and Jan 24). As such,  removing month gives their combined total rather than just the latest

Hello @MKSMPB ,

Does that mean that Month is a dimension in Metric 3? I did not mean removing it as with REMOVE, but from the dimensionality of the Metric. When using [BY LASTNONBLANK : Month] you will be taking data from the latest Month, so you do not need to have Month as a dimension in the target metric (Metric 3)

Cheers!

If i remove it from the dimensionality it flags up an error. It seems as though lastnonblank is not doing as it is intended

Hello,

Are the dimensions in Metric 2 and Metric 3 the same with the only change being that Metric 3 is not dimensioned by Month?

And also, what is the flag telling you?

Cheers!

Yes they’re the same. 

Just that Dimension ‘Month’ was removed

Success! Thanks for all your help Bruno!