Monthly Average of Previous 5 Years

[iamAkashSingh]

I have a data in below format.

The Data is for every day.

Date,Region,Refinery,CapacityShutdown

2018-01-01,Africa,GP Oil, 26

2018-01-02,Africa,GP Oil, 27

2018-01-03,Africa,GP Oil, 26

For previous 10 Years and for future years as well.

I want to show a seasonality graph like mentioned in the below picture.

Along with this, I need filter on Region as well. 

Measures: Previous 5 Year monthly high, Previous 5 Year monthly low (Shaded region from low to high), 5 Year monthly average, previous year monthy shutdown, current year monthly shutdown.

X Axis : Months (Jan to Dec)

Y Axis : Capacity shutdown.

How to calculate this measures using DAX?

Regards,

Akash

[v-chuncz-msft · Accepted solution]

@iamAkashSingh,

You may try the following measure.

Measure =
AVERAGEX (
    SUMMARIZE (
        FILTER (
            Table1,
            YEAR ( Table1[Date] )
                >= YEAR ( TODAY () ) - 5
                && YEAR ( Table1[Date] ) < YEAR ( TODAY () )
        ),
        Table1[Date].[Year]
    ),
    CALCULATE ( SUM ( Table1[CapacityShutdown] ) )
)