Forum Discussion
Solved
Rolling quarters
Hello, I am not sure if I am using the terminology for this post with saying rolling quarters. What I would like to achieve is a graph that shows year over year data but for the last bar I want i...
Hi jjasper
Create a calendar table
calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"day",DAY([Date]))
Create a relationship as below
Create measures in Sheet2
value per year = CALCULATE(SUM(Sheet2[value]),ALLEXCEPT('calendar','calendar'[year])) 2018Q2~Q4 = CALCULATE ( SUM ( Sheet2[value] ), FILTER ( ALL ( 'calendar' ), 'calendar'[year] = 2018 && 'calendar'[month] >= 4 && 'calendar'[year] = MAX ( 'calendar'[year] ) - 1 ) ) 2019Q1 = CALCULATE ( SUM ( Sheet2[value] ), FILTER ( ALL ( 'calendar' ), 'calendar'[year] = 2019 && 'calendar'[month] <= 3 && 'calendar'[year] = MAX ( 'calendar'[year] ) ) ) total value = IF(MAX('calendar'[year])<>2019,[value per year],[2018Q2~Q4]+[2019Q1]) average = [total value]/12Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi jjasper
Do you like a visual like this?
The average value should be :
for 2016: (total value in 2016)/12 (numbers of months in a year)
the same ofr 2017 and 2018,
but for 2019, the average value should be:
total value from 2018 Q2(2018/4/1~) to 2019 Q1(2019/3/31)
Divide
12 (numbers of months in period above)
Is my understanding correct?
If it is correct, i can go on to find the final solution.
Best Regards
Maggie
Yes that is exactly what I am looking for.
