LastPeriods; what about the first months

[Eric Hans van Wingerden 4]

If I use LastPeriods in a line chart to calculate a 12 month sum this works nicely apart from the first 12 months where there is no data selected for the 12 preceding months: (See picture in attachment)

 

So here, I selected 2019 and 2020, which causes 2019 not to have the preceding 12 months. Does anyone know how to solve this

[David Boot-Olazabal]

Hi Eric,

You could try to create a calculated column that has the lowest selected year + that lowest selected year -1. That way, if you then select 2019 and 2020, the 2019 values would include the 2018 values (if that is what is needed).

Kind regards,

David

[Eric Hans van Wingerden 4]

Hey David,

Long time no speak! So, I use this custom expression in the graph:

Sum([LTI]) THEN SUM([Value]) OVER (LastPeriods(12,[Axis.X])) THEN If(Count() OVER (LastPeriods(12,[Axis.X]))=12,[Value],[Value]), Sum([LTI])

So how is your solution going to help then Appreciate yo uthinking with me!

[Eric Hans van Wingerden 4]

Hey David,

 

Long time no speak! So, I use this custom expression in the graph:

 

Sum([LTI]) THEN SUM([Value]) OVER (LastPeriods(12,[Axis.X])) 

 

So how is your solution going to help then Appreciate you thinking with me!

[Eric Hans van Wingerden 4]

Added:

 

THEN If(Count() OVER (LastPeriods(12,[Axis.X]))=12,[Value],NULL) to suppress the invalid (firstyear) values so only those with 12 months are shown, resulting in the correct numbers BUT a partly empty graph....

[David Boot-Olazabal]

Hi Eric,

 

Indeed, long time no speak. Can you elaborate a bit more on what you are trying to achieve What is the LastPeriod function supposed to do If I use your formula, it looks like it is some kind of running sum function. Which indeed seems not to be working in that form.

 

Kind regards,

David