Forum Discussion
Helper VExact 12 month rolling
Hello...
I need exact 12 month rolling data. I have data from January 2019 to May 2020. In my data some month are missing in 2019 (may 2019 and june 2019). So I want include that missing month also for last 12 month Rolling.
I have craeted measure :
last 12 month average = CALCULATE(
AVERAGE(employee[value]),
DATESINPERIOD( 'employee'[Date], MAX( 'employee'[Date] ), -12, MONTH ),VALUES(employee[Date])
)
but this measure is showing me 29.9 average but actual average 26.66 some thing.
last 12 month will be May 2020 - June 2019 average will be 26.66 something but my measure is calculating 29.9. it is skipping the missing month may 2020 and june 2020 and after august 2019 it is considering april 2019.
Below file is attached Excel and PBIX file both:
PBIX:
https://drive.google.com/file/d/1BBxzwGkgUAax5pmLC77uBsV6kS_PHguV/view?usp=sharing
Excel:
https://drive.google.com/file/d/1uxuTWnh45xi5XpytyqgSueaqLZhO9u6J/view?usp=sharing
Hello @mohittimpus ,
You can do this.
last 12 month average = VAR x = CALCULATE( SUM(employee[value]), DATESINPERIOD( employee[Date], MAX(employee[Date]), -12, MONTH ) ) RETURN x/12Best regards
Lionel ChenIf this post helps,then consider Accepting it as the solution to help other members find it faster.
4 Replies
- amitchandak
Super User
Try to use a date table and values in averageX
last 12 month average = CALCULATE(
AVERAGEX(VALUES(employee[Date]),employee[value]),
DATESINPERIOD( 'Date'[Date], MAX( 'employee'[Date] ), -12, MONTH )
)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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos. - Greg_Deckler
Community Champion
mohittimpus You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008 
v-lionel-msftCommunity Support
Hello @mohittimpus ,
You can do this.
last 12 month average = VAR x = CALCULATE( SUM(employee[value]), DATESINPERIOD( employee[Date], MAX(employee[Date]), -12, MONTH ) ) RETURN x/12Best regards
Lionel ChenIf this post helps,then consider Accepting it as the solution to help other members find it faster.
mohittimpusI want to show month name may 2020-june 2019. How to show 12 month name with above measure value.
