cancel
Showing results for
Did you mean:
Frequent Visitor

## Current month vs 3yr same period average

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 %

my data format setup

trying to achieve similar like this

1 ACCEPTED SOLUTION
Community Support

Hi @Kannan4444 ,

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.

3 REPLIES 3
Community Support

Hi @Kannan4444 ,

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.

Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

@Greg_Deckler  i have calendar in the above format.

getting error, am lost 😞

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors