Current month vs 3yr same period average

[Kannan4444]

Hey!! am trying to show current month compared against previous 3 yrs same month average 

Formula am trying to bring in power bi = "Nov 2022 / (Average of Nov 2021, Nov 2020, Nov 2019) -  1 " to be show in %

can you please hep!!!

my data format setup

trying to achieve similar like this

[v-binbinyu-msft · Accepted solution]

Hi @Kannan4444 ,

Please try below steps:

1. below is my test table

Table:

Table2:

create with below dax formula:

Table 2 = {1,2,3,4,5,6,7,8,9,10,11,12}

2. create measure with below dax formula

2022 =
VAR max_date =
    MAXX ( ALL ( 'Table' ), [Date] )
VAR cur_year =
    YEAR ( max_date )
VAR cur_month =
    SELECTEDVALUE ( 'Table 2'[Value] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year
            && MONTH ( 'Table'[Date] ) = cur_month
    )
RETURN
    SUMX ( tmp, [Volume] )

3 yr Average =
VAR max_date =
    MAXX ( ALL ( 'Table' ), [Date] )
VAR cur_year =
    YEAR ( max_date )
VAR cur_month =
    SELECTEDVALUE ( 'Table 2'[Value] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 1
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR tmp1 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 2
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR tmp2 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 3
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR _val =
    SUMX ( tmp, [Volume] )
VAR _val2 =
    SUMX ( tmp1, [Volume] )
VAR _val3 =
    SUMX ( tmp2, [Volume] )
VAR _volumme =
    DIVIDE ( _val + _val2 + _val3, 3 )
VAR _a = [2022]
RETURN
    DIVIDE ( _a, _volumme )

5 yr Average =
VAR max_date =
    MAXX ( ALL ( 'Table' ), [Date] )
VAR cur_year =
    YEAR ( max_date )
VAR cur_month =
    SELECTEDVALUE ( 'Table 2'[Value] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 1
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR tmp1 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 2
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR tmp2 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 3
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR tmp3 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 4
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR tmp4 =
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = cur_year - 5
            && MONTH ( 'Table'[Date] ) = cur_month
    )
VAR _val =
    SUMX ( tmp, [Volume] )
VAR _val2 =
    SUMX ( tmp1, [Volume] )
VAR _val3 =
    SUMX ( tmp2, [Volume] )
VAR _val4 =
    SUMX ( tmp3, [Volume] )
VAR _val5 =
    SUMX ( tmp4, [Volume] )
VAR _volumn =
    DIVIDE ( _val + _val2 + _val3 + _val4 + _val5, 5 )
VAR _a = [2022]
RETURN
    DIVIDE ( _a, _volumn )

3. add a chart named "Line and clustered column chart" with field and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Kannan4444 I don't see an actual date in your data. But, in theory you could do something like this:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __12MonthsAgo = EOMONTH(__Date, -12)
  VAR __24MonthsAgo = EOMONTH(__Date, -24)
  VAR __36MonthsAgo = EOMONTH(__Date, -36)
  VAR __Volume = SUMX(FILTER('Table',[Month] = MONTH(__Date) && [Year] = YEAR(__Date)),[Volume])
  VAR __Volume12MonthsAgo = SUMX(FILTER('Table',[Month] = MONTH(__12MonthsAgo ) && [Year] = YEAR(__12MonthsAgo )),[Volume])
  VAR __Volume24MonthsAgo = SUMX(FILTER('Table',[Month] = MONTH(__24MonthsAgo ) && [Year] = YEAR(__24MonthsAgo )),[Volume])
  VAR __Volume36MonthsAgo = SUMX(FILTER('Table',[Month] = MONTH(__36MonthsAgo ) && [Year] = YEAR(__36MonthsAgo )),[Volume])
  VAR __Average = DIVIDE( __Volume12MonthsAgo + __Volume24MonthsAgo + Volume36MonthsAgo, 3)
  VAR __Result = DIVIDE(__Volume, __Average)
RETURN
  __Result

[Kannan4444]

@Greg_Deckler  i have calendar in the above format. 

getting error, am lost 😞