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.
Hi, I need to create measure that calculate moving average for the last 6 months, but the date is not always sequencial
for instance, In the Table below
Scenario 1: If I choose Jun 2018, the measure will display value average last 6 months, but with calculate from column that value is "Has Sales=Yes". That is (Oct 2017 + Nov 2017 + Dec 2017 + Jan 2018 + Mar 2018 + May 2018) / 6 = (19+20+21+22+24+26)/6
Scenario 2: If I choose March 2018, the measure will display value average last 6 months, but with calculate from column that value is "Has Sales=Yes". That is (Jul 2017 + Sep 2017 + Oct 2017 + Nov 2017 + Dec 2017+ Jan 2018) / 6 = (16+18+19+20+21+22)/6
Month | Has Sales | Value |
Jan-17 | Yes | 10 |
Feb-17 | Yes | 11 |
Mar-17 | No | 12 |
Apr-17 | No | 13 |
May-17 | Yes | 14 |
Jun-17 | Yes | 15 |
Jul-17 | Yes | 16 |
Aug-17 | No | 17 |
Sep-17 | Yes | 18 |
Oct-17 | Yes | 19 |
Nov-17 | Yes | 20 |
Dec-17 | Yes | 21 |
Jan-18 | Yes | 22 |
Feb-18 | No | 23 |
Mar-18 | Yes | 24 |
Apr-18 | No | 25 |
May-18 | Yes | 26 |
Jun-18 | Yes | 27 |
I found the solution in this link
https://community.powerbi.com/t5/Desktop/Average-of-last-6-months/td-p/128738
But This wont work in my case because the Datesinperiod function in above links only give statics last N months
Any idea would be really appreciated
Thanks !
Solved! Go to Solution.
Try this MEAASURE
Measure = VAR mydate = SELECTEDVALUE ( Table1[Month] ) VAR Previous6 = TOPN ( 6, FILTER ( ALL ( Table1 ), Table1[Month] < mydate && Table1[Has Sales] = "Yes" ), [Month], DESC ) RETURN AVERAGEX ( Previous6, [Value] )
Hi @andrehawari,
By my test, the measure from @Zubair_Muhammad should be the solution.
If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
Try this MEAASURE
Measure = VAR mydate = SELECTEDVALUE ( Table1[Month] ) VAR Previous6 = TOPN ( 6, FILTER ( ALL ( Table1 ), Table1[Month] < mydate && Table1[Has Sales] = "Yes" ), [Month], DESC ) RETURN AVERAGEX ( Previous6, [Value] )
See attached file as well
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |