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.
Hello,
I know there are a number of questions on Rolling 12 months already, However, i was not able to find one that fits my needs.
I need to create a bar chart with months on x-axis and values on y-axis as mentioned below:
Show last 12 months until previous month (Since we are in Feb now, the chart should show Jan-21 to Feb-20 on x-axis)
Calculation:
Each bar in the chart should display value as Jan-21 = (Jan-21 + Dec-20 +…. + Mar-20 + Feb-20)/12
Similarly for Dec-20 = (Dec-20 + Nov-20 +…. + Feb-20 + Jan-20)/12
Nov-20 = (Nov-20 + Oct-20 +…. + Jan-20 + Dec-19)/12
.
.
.
Feb-20 = (Feb-20 + Jan-20 +…. + Apr-19 + Mar-19)/12
Source Data:
Month-Year | Value |
Jan-19 | 50 |
Feb-19 | 30 |
Mar-19 | 10 |
Apr-19 | 60 |
May-19 | 30 |
Jun-19 | 20 |
Jul-19 | 10 |
Aug-19 | 10 |
Sep-19 | 40 |
Oct-19 | 70 |
Nov-19 | 40 |
Dec-19 | 50 |
Jan-20 | 60 |
Feb-20 | 30 |
Mar-20 | 40 |
Apr-20 | 60 |
May-20 | 20 |
Jun-20 | 30 |
Jul-20 | 70 |
Aug-20 | 10 |
Sep-20 | 30 |
Oct-20 | 50 |
Nov-20 | 20 |
Dec-20 | 30 |
Jan-21 | 50 |
Feb-21 | 20 |
Required (in a bar chart):
Sum | Sum/12 | |
Feb-20 | 430 | 35.83333 |
Mar-20 | 460 | 38.33333 |
Apr-20 | 460 | 38.33333 |
May-20 | 450 | 37.5 |
Jun-20 | 460 | 38.33333 |
Jul-20 | 520 | 43.33333 |
Aug-20 | 520 | 43.33333 |
Sep-20 | 510 | 42.5 |
Oct-20 | 490 | 40.83333 |
Nov-20 | 470 | 39.16667 |
Dec-20 | 450 | 37.5 |
Jan-21 | 440 | 36.66667 |
Thank you in advance.
Solved! Go to Solution.
Hi @Anonymous ,
Here are the steps you can follow:
1. Create measure.
Sum =
var _1=
IF(MAX([month])<>12,
CALCULATE(MAX('Table'[Month-Year]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[month]=MAX('Table'[month])+1)),
DATE(YEAR(MAX([Month-Year])),1,1))
var _2=
CALCULATE(
SUM('Table'[Value]),
FILTER(ALL('Table'),[Month-Year]>=_1&&[Month-Year]<=MAX([Month-Year])))
return _2
Sum/12 =
var _1=
IF(MAX([month])<>12,
CALCULATE(MAX('Table'[Month-Year]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[month]=MAX('Table'[month])+1)),
DATE(YEAR(MAX([Month-Year])),1,1))
var _2=
CALCULATE(
SUM('Table'[Value]),
FILTER(ALL('Table'),[Month-Year]>=_1&&[Month-Year]<=MAX([Month-Year])))
return _2/12
2. Result
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
When you say "Show last 12 months until previous month", then for the December 2020 column of the chart shouldn't the range for calculation be 1 December 2019 to 30 November 2020? Why have you have mentioned the range as 1 January 2020 to 31 December 2020?
See if my Blog here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hi Ashish,
For the calculation of sum, it has to consider 12 months backwards including the respective month i.e.,
for Jan 2021 = Jan 21 + Dec 20 + Nov 20 + ........ + Feb 20
However, in the chart, i need only the last 12 months starting from previous month on the x-axis i.e., since we are in Feb 21 now, the chart should show data for Jan 21, Dec 20, Nov 20..... Feb 20
Hi @Anonymous ,
Here are the steps you can follow:
1. Create measure.
Sum =
var _1=
IF(MAX([month])<>12,
CALCULATE(MAX('Table'[Month-Year]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[month]=MAX('Table'[month])+1)),
DATE(YEAR(MAX([Month-Year])),1,1))
var _2=
CALCULATE(
SUM('Table'[Value]),
FILTER(ALL('Table'),[Month-Year]>=_1&&[Month-Year]<=MAX([Month-Year])))
return _2
Sum/12 =
var _1=
IF(MAX([month])<>12,
CALCULATE(MAX('Table'[Month-Year]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[month]=MAX('Table'[month])+1)),
DATE(YEAR(MAX([Month-Year])),1,1))
var _2=
CALCULATE(
SUM('Table'[Value]),
FILTER(ALL('Table'),[Month-Year]>=_1&&[Month-Year]<=MAX([Month-Year])))
return _2/12
2. Result
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the solution, this works just right.
@Anonymous , Try measure like this with date table
divide( CALCULATE([sum],DATESINPERIOD('DateTable'[Date ],MAX('DateTable'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), not(isblank([sum]))))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi Amit,
Thank you for the response.
I tried your measure, but i get this error in DAX
Could you please explain what is the denominator part of your formula trying to calculate?
i.e., CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), not(isblank([sum])))
@Anonymous , Try like
divide( CALCULATE([sum],DATESINPERIOD('DateTable'[Date ],MAX('DateTable'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), filter('Table',not(isblank([sum])))))
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |