cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Struggling with rolling average (bizarre)

Hi, I am struggling with this, I'm practicing Power BI, have two tables, Calendar and Sales, Calendar table extends dates till december 2011, while Sales tables dates only go to december 2009.

When I try to build a rolling average 3 months, results extends to february/2010, which is incorrect.

 

I know, it could be solved just filtering or removing non existent dates from Calendar table, but it is a challenge on how to control this situation, maybe it is unprobable in real life, but I'd like to know if it is possible workaround this without delete or filter anything.

 

This is what I did for rolling average

 

Movil3MesesListo =
CALCULATE(
         SUM(FactSales[Cantidad Total]),
         DATESINPERIOD(DimCalendar[DateKey],LASTDATE(DimCalendar[DateKey]),-3,MONTH)
)/ CALCULATE(
         DISTINCTCOUNT(DimCalendar[Mes]),
         DATESINPERIOD(DimCalendar[DateKey],LASTDATE(DimCalendar[DateKey]),-3,MONTH)
)
 
Many thanks in advance

DudaRollingAverage.jpgDudaRollingAverage2.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Struggling with rolling average (bizarre)

Hey @RolandoVG ,

 

CALCULATE(...) has to used whenever an existing filter context has to be modified. As you are going to calculate the rolling average for the last three months, you have to use CALCULATE.

 

The 1st parameter is the easy part, it's the numeric expression, here SUM(...), the 2nd to nth parameter are the filter modifier. Here you are using DATESINPERIOD(...).

What happens in January is this. DATESINPERIOD returns a table that contains the dates for 1st, of November 2009 to 31, of January 2010. This means the SUM is created adding (1253... + 1301... + null) / 3.

The same happens in February 2010 
This means the SUM is created adding (1301... + null + null) / 3.

I say this is correct, this is how DAX works, but maybe this is not what you are expecting 😉

 

If you want to avoid the calculation if SUM('...'[Cantidad Total]) is null then you have to wrap the complete calculation into an if statement like so:

measure = 
IF(NOT(ISBLANK(SUM('...'[Cantidat Total]))
, CALCULATE(...)
, BLANK()
)

Hopefully, this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: Struggling with rolling average (bizarre)

Hey @RolandoVG ,

 

CALCULATE(...) has to used whenever an existing filter context has to be modified. As you are going to calculate the rolling average for the last three months, you have to use CALCULATE.

 

The 1st parameter is the easy part, it's the numeric expression, here SUM(...), the 2nd to nth parameter are the filter modifier. Here you are using DATESINPERIOD(...).

What happens in January is this. DATESINPERIOD returns a table that contains the dates for 1st, of November 2009 to 31, of January 2010. This means the SUM is created adding (1253... + 1301... + null) / 3.

The same happens in February 2010 
This means the SUM is created adding (1301... + null + null) / 3.

I say this is correct, this is how DAX works, but maybe this is not what you are expecting 😉

 

If you want to avoid the calculation if SUM('...'[Cantidad Total]) is null then you have to wrap the complete calculation into an if statement like so:

measure = 
IF(NOT(ISBLANK(SUM('...'[Cantidat Total]))
, CALCULATE(...)
, BLANK()
)

Hopefully, this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Highlighted
Regular Visitor

Re: Struggling with rolling average (bizarre)

Many thanks @TomMartens , that worked very well.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors