Forum Discussion
Rolling average
Hello!! Community. Iam facing problem in one visual table i'll need your help. There are 3 columns in the table Sheets1; The columns are Sales Date, Total Sales, Region. I want to calculate 3 month rolling average i had created the Dax Measure:
But in the results it is showing Rolling avgg values along with 2 extra months i.e august, september for region mumbai and may, june for banglore which is not there in the dataset. I dont want these rolling avgg with extra months in the results and i dont want to create calendar dimension table. I had attached the result image. Please Help
5 Replies
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence PlaylistOnce you have date dimension, add this measure:
Rolling avgg = CALCULATE(average(Sheet1[total sales]), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH))parry2k Thanks for the reply and your youtube channels. I had Created Date dimension table and created relationship but again same error. Iam sharing detail screenshot please have a look.
tsurugi824 share the measure definition, and also make sure date-related columns are used from the date dimension.
parry2k This is the measure and screenshot attached below:
Rolling avgg = CALCULATE(average(Sales[total sales]), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH))
