Rolling Quarter in a Pivot Table

[50ShadesOfSalty]

Hi everyone,

I'm trying to build a measure that considers a rolling quarter per month. 

Imagine:

• Nov 20: Should contain values for Sep20, Oct20 and Nov20
• Dec 20: Should contain values for Oct20, Nov20 and Dec20
• Jan 21: Should contain values for Nov20, Dec20 and Jan21
• and so on and before.

My main question is: Can we do this type of analysis in a Pivot Table with Months as columns?

Find below an illustrative example of the Pivot Table i'm looking for:

Thank you for the help!

[Ksrinivasan]

hi,

yes its possible, 

i have took day in my example, you can change instead of month

to get current day value syntax:

Sum({1<[Date_01] = {"$(=Max([Date_01]))"}>} PLANT_WIP_QTY),

Previous day value Syntax:

Sum({1<[Date_01] = {"$(=date((Max([Date_01])-1),'DD/MM/YYYY'))"}>} PLANT_WIP_QTY)

Result will be selected Date value and previous day value

ksrinivasan

[50ShadesOfSalty]

Hi @Ksrinivasan , 

Your proposed solution would be something similar as:

Sum({< Month={">=$(=Date(AddYears(AddMonths(today(), -2), -1), 'YYYYMM')) <=$(=Date(AddYears(today(), -1), 'YYYYMM'))"}>}  PLANT_WIP_QTY)

Which, instead of doing a Rolling Quarter for all the months in the table (it can be up to 36 months), displays only 3 months...

Thank you for the help!