Rolling average

tsurugi824 · ‎10-04-2023

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:

Rolling avgg = CALCULATE(average(Sheet1[total sales]), DATESINPERIOD(Sheet1[sales date ].[Date], LASTDATE(Sheet1[sales date ].[Date]), -3, MONTH))

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

parry2k · ‎10-04-2023

@tsurugi824 you missed this video:

Mark date dimension as a date table - why and how?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k · ‎10-04-2023

@tsurugi824 share the measure definition, and also make sure date-related columns are used from the date dimension.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

tsurugi824 · ‎10-04-2023

@parry2k This is the measure and screenshot attached below:

Rolling avgg = CALCULATE(average(Sales[total sales]), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH))

parry2k · ‎10-04-2023

@tsurugi824

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 Playlist

Once you have date dimension, add this measure:

Rolling avgg = CALCULATE(average(Sheet1[total sales]), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH))

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

tsurugi824 · ‎10-04-2023

@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.