Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Kannan4444
Helper I
Helper I

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

 

2 ACCEPTED SOLUTIONS

Kannan4444_0-1670253273436.png

@Greg_Deckler  i have calendar in the above format. 

getting error, am lost 😞

Kannan4444_1-1670253322337.png

 

View solution in original post

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.