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

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.