cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kannan4444
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 %

 

can you please hep!!!

 

my data format setup

Kannan4444_0-1669992374915.png

trying to achieve similar like this

Kannan4444_1-1669992387872.png

 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @Kannan4444 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_1-1670832928149.png

 

Table2:

create with below dax formula:

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

vbinbinyumsft_0-1670832906972.png

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

vbinbinyumsft_2-1670833194932.png

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.

View solution in original post

3 REPLIES 3
v-binbinyu-msft
Community Support
Community Support

Hi @Kannan4444 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_1-1670832928149.png

 

Table2:

create with below dax formula:

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

vbinbinyumsft_0-1670832906972.png

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

vbinbinyumsft_2-1670833194932.png

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
Super User
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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Kannan4444_0-1670253273436.png

@Greg_Deckler  i have calendar in the above format. 

getting error, am lost 😞

Kannan4444_1-1670253322337.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.