Forum Discussion

jjasper's avatar
jjasper
Helper II
7 years ago
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 it to show a rolling quarter. So for example I want a graph that show 2016 average, 2017 average, 2018 average but for 2019 is show the average from Q2 2018 to Q1 2019. I am not sure how to achieve this. I thought using the calculation for a rolling average would do the trick but I don't it is right because there is data for 2018 but when I do the calculation it doesn't show data for 2019. I know there is no data for Q1 2019 but there is data for 2018 so I would assume if it is working it would show a bar that has data in it.

 

I hope this makes sense.

 

TIA

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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]/12

     

    Best Regards
    Maggie

     

    Community 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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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

    • jjasper's avatar
      jjasper
      Helper II
      7 years ago

      Yes that is exactly what I am looking for. 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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]/12

     

    Best Regards
    Maggie

     

    Community 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.