Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
realdanielbyrne
Frequent Visitor

Rolling Average Averagex, Time Intelligence with Date Table is incorrectly ignoring zero sales day

 

The Dates Table is linked to the Sales table by Date. Date Table:

Dates = CALENDARAUTO() 

 

Total Sales from Fact Table:

Total Sales = Sum[Sales]

 

My 14 day rolling average DAX 

14 Day Average = AVERAGEx(DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-14,DAY),[Total Sales])

 

The Date is filtered to 8/13/2018 to 8/31/2018. 

Results:

 

Equipment14 Day Average
BD11007$362

 

EquipmentDateTotal Sales
BD110078/31/2018 0:00$310
BD110078/25/2018 0:00$310
BD110078/18/2018 0:00$465

 

The average over those 14 days should be total sales / 14 days

1086/14 = 77.5

However the dax above is calculating Total Sales / Days With Sales

1086/3 = 362

What strikes me as odd is that from Quick Measures to numerous blogs and YouTube videos it appears as if my Dax is the recommended calculation.  However, it is also clear that it is blatently wrong.

 

I am at a loss for explaining how the calculation can be recommended, but the result incorrect.  What explains this apparent contradiction?  

 

Likewise basic calculations like Average Sales / Day are also incorrect because averages seem to always be based upon if a sales actually happened.  This seems like either a major oversight in PowerBI / DAX or a major misunderstandig on my part.

 

 

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @realdanielbyrne,

 

The solution of  Chihiro should meet your requirement.

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Chihiro
Solution Sage
Solution Sage

Average calculation will not consider blanks. Meaning dates without data will be excluded from average calculation.

 

This is intended behaviour, and useful when calculating average that includes dates with no expected sales (i.e. store/business closed on holidays etc).

 

But for your purpose, this isn't desired. Since 14 is fixed. Try changing it to SUMX()/14.

 

Edit: or more appropriately DIVIDE(SUMX(),14,0)

Thank you for the clarification; although, that particular point is not mentioned in ANY documentation I can find on Averagex or the time intelligence functions.  Also that rationalization doesn't make sense.  

 

When averaged over all sales for a company for every product category, this might be accecptible.  However, this behavior quickly breaks down when you start considering product segments or departments. Many product or server categories are infrequently sold.  Think mortgages, roofing jobs, cars, airplanes, trains, dirt, accounting services, etc.  It is a false equivalence to assume that because a product was not sold on a day than it must be a weekend or a holiday.

 

For instance If Boeing were to use Averagex track the daily averages sales for 747s, the calculated result would be WAY off.  They may only sale 1 a quarter. 

 

In my particular industry we are heavy tracking equipment utilization.  A heavy equipment hauler might genrate $3000 a day, but only work 3 days in a week.  However, there are expenses such as debt servicing, insurance, and maintenance that accure daily. As such we need to track the weekly and monthly revenue averages that a machine is generating in to make sure it is turning a profit.

 

I'm new to PowerBi, buy i've been a professional programmer for decades, and it boggles my mind that this unwritten behavior is acceptable.  There is no telling how many incorrect dashboards have been built around the world based upon the misconception that AVERAGEX(DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-7,DAY),[Total Sales]) does NOT in fact return an average of total sales over the unbroken, uninterrupted date range that I pass into the function!  It is exacly like Microsoft saying "4/2  = 2 but there are exceptions where sometimes it's 4 or 1, but those exceptions are not spelled out or detailed anywhere.  Good luck in finding them."

 

For those searching why your time intelligence functions are not working properly, here is a working DAX implementation of a true 2 week moving average.

 

 

2 Week Moving Average = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-14, DAY))
    /
CALCULATE(
    DISTINCTCOUNT(Dates[Date]),
    DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-14, DAY))

 

If you want to filter out working days from nonworking days, the proper procedure is to create a class label in your date table to which you can group, filter and calculate across.  So for instance you can cateorgize your dates into "WorkDays" and "OffDays".  In which case the above calculation becomes:

2 Week Moving Average = 
CALCULATE(
    [Total Sales],
    DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-14, DAY),
    FILTER(ALLSELECTED(Dates),Dates[Day Type] = "WorkDay"))
    /
CALCULATE(
    DISTINCTCOUNT(Dates[Date]),
    DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-14, DAY),
    FILTER(ALLSELECTED(Dates),Dates[Day Type] = "WorkDay"))

 

 

Averagex and the other time intelligence functions could be easily fixed to correctly accomodate nonworking days, by accepting an optional filter parameter.  Until then however I'm afraid that recreating these base methods is the only alternative.

 

 

 

Thank you!

It's pointed out here.

https://msdn.microsoft.com/en-us/query-bi/dax/averagex-function-dax

"...

In all other respects, AVERAGEX follows the same rules as AVERAGE. You cannot include non-numeric or null cells. Both the table and expression arguments are required.

..."

 

From AVERAGE function:

https://msdn.microsoft.com/en-us/query-bi/dax/average-function-dax

"...

Nonnumeric values in the column are handled as follows:

  • If the column contains text, no aggregation can be performed, and the functions returns blanks.

  • If the column contains logical values or empty cells, those values are ignored.

  • Cells with the value zero are included.

  • When you average cells, you must keep in mind the difference between an empty cell and a cell that contains the value 0 (zero). When a cell contains 0, it is added to the sum of numbers and the row is counted among the number of rows used as the divisor. However, when a cell contains a blank, the row is not counted.

..."

 

Basically difference between "Zero" value in a field versus empty/null value in a field.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.