Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |