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
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
Solved! Go to 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.
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.
@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
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |