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
MatWebb
Advocate I
Advocate I

Rolling average across a Distinct calculation showing incorrect results

Hi Everyone,

 

hopefully this should be a simple one...

 

Some background: my dataset is for a logistics company, where there is one huge fact table (shipments) and many dimentions tables including 'type' of cargo, loading location, delivery location, Load date, Delivery date, Vehicle size etc.

 

 

I'm trying to create a rolling 3months avarage on the number of unique vehicles used to make deliveries per month.

 

note: A single vehicle will make many deliverys each month and the specific requirement here is that a unique count (distinctcount) of the number of vehicles used per month is charted. 

 

Its really simple to chart the distinct number of vehicles used per month, however when coming to create the rolling average if this value across a 3 month period i am having issues with the filter context and instead of taking each individual monthly value and avarageing them, my measure is looking across the 3 month window and doing a distinct count on that figure instead, creating an incorrect dataset. heres the example data:

 

To highlight the issue - if we look at Aug-Oct the total no. of Vehicles across these 3 months = 82, therefore the avarage for that period should be 82/3 = 27.3333333 - however as you can see in the 3rd column (the measure i am trying to write) it is slightly out for October (i.e. the 3 month period aug-oct) at a value 26.67. This is the case for most of the values in the table, and i have tracked the issue to this incorrect filtering.

 

Here's my Dax, it references the following tables:

- Shipments - the main fact table that holds all the shipment records as mentioned above

- PlacesD - list of countries and depots for Destination
- PlacesL - list of countries and depots for Loading

- DateTable - my Calendar table

m_rAvg12_NoVehicles_Month = 
var month = 3

return
CALCULATE(
	  DIVIDE(
CALCULATE( CALCULATE(
DISTINCTCOUNT(Shipments[ImoNo]),//distinct count on the vehicle id
filter(PlacesD, PlacesD[CountryCodeD] <> DISTINCT(PlacesL[CountryCode]))//filter to remove domestic shipments
), all(Shipments[LoadDate_DateOnly]), //Make sure any other filtering on other measures that use this is removed FILTER ( all ( DateTable ),//use the calendar table DateTable[firstDayOfMonth] >= EOMONTH(MAX(Shipments[LoadDate_DateOnly]), -month) //create window of time && DateTable[firstDayOfMonth] <= MAX(Shipments[LoadDate_DateOnly])//Cap the window at most recent date ) ),
month), DATESBETWEEN( //trimming off incomplete months from the beginning Shipments[LoadDate_DateOnly], DATEADD(FIRSTDATE(all(Shipments[LoadDate_DateOnly])),month-1,MONTH), //start date LASTDATE(DateTable[Date]) //end date ) )

 

 It's worth mentioning that the result will be filtered by many of the dimentions tables, i attempted to solve this problem with a 'summarizecolumns' into a new table hower the relationships felt like an issue when trying to use the slicers.

 

I feel like i have made a stupid error somewhere - if ihave missed any detail then please do say and ill happily fill in the blanks.

Please help me see the light!!
Hoping you can help

 

Mat

1 ACCEPTED SOLUTION

Hi @MatWebb,

 

This one will also work

 

=IF(DATEDIFF(CALCULATE(MIN(DateTable[firstDayOfMonth]),ALLSELECTED(DateTable[firstDayOfMonth])),MAX(DateTable[firstDayOfMonth]),MONTH)<=1,BLANK(),if(HASONEVALUE(DateTable[firstDayOfMonth]),if(ISBLANK([m_Calc_NumberShips_Export]),BLANK(),AVERAGEX(CALCULATETABLE(VALUES(DateTable[firstDayOfMonth]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-2),MAX(DateTable[Date]))),[m_Calc_NumberShips_Export])),BLANK()))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi @MatWebb,

 

Try this measure for computnig the rolling three month average

 

=CALCULATE(AVERAGE([No. Unique vehicles]),DATESBETWEEN(DateTable[Month/Year],EDATE(MIN(DateTable[Month/Year]),-2),MAX(DateTable[Month/Year])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for Replying with a suggestion @Ashish_Mathur, unfortunatly this doesn't work as dont have a 'unique number of vehicles' column. I can create a measure for this (Distinctcount() ) but then that cant go on the Average() formula.

 

Have i missed something?

 

thanks in advance

Hi,

 

Please share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-jiascu-msft
Employee
Employee

Hi @MatWebb,

 

I made some changes of your formula. Please try it out. But I don't know if it's correct. Please share a dummy pbix file.

m_rAvg12_NoVehicles_Month =
VAR month = 3
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( Shipments[ImoNo] ),
            //distinct count on the vehicle id
            FILTER (
                PlacesD,
                NOT PlacesD[CountryCodeD] IN DISTINCT ( PlacesL[CountryCode] )
            ),
            DATESINPERIOD ( 'datetable'[date], MAX ( 'datetable'[date] ), - month, MONTH )
        ),
        month
    )

Best Regards!

Dale

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

Thanks for your suugested edit @v-jiascu-msft but unfortunatly it didnt work - it presented the same answer (it worked as some streamlined dax if nothing else!)

 

Here's a link to a dummy file setup with the same problem hopefully this will help you spot my error? @Ashish_Mathur @v-jiascu-msft

https://drive.google.com/file/d/1We8QpuCEjEFlB8yD7IasupOlnkYTIJsG/view?usp=sharing

 

Thanks so much for helping with this guys, Keeping my fingers crossed

 

Mat

Hi @MatWebb,

 

This one will also work

 

=IF(DATEDIFF(CALCULATE(MIN(DateTable[firstDayOfMonth]),ALLSELECTED(DateTable[firstDayOfMonth])),MAX(DateTable[firstDayOfMonth]),MONTH)<=1,BLANK(),if(HASONEVALUE(DateTable[firstDayOfMonth]),if(ISBLANK([m_Calc_NumberShips_Export]),BLANK(),AVERAGEX(CALCULATETABLE(VALUES(DateTable[firstDayOfMonth]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-2),MAX(DateTable[Date]))),[m_Calc_NumberShips_Export])),BLANK()))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur you are a Star!

 

They both worked (which is better than i achieved).

Do you have any comments on which version to use? Im thiking with performance in mind, calculatedtable() feels better than summarize()?

 

Its amazing we needed this beast of a formula to do something quite striaghtforward, any advice/comment on how i could have set this up to make it easier?

 

thansk again, really appreciate it!

 

Mat

Hi,

 

You are welcome.  Thank you for your kind words.  Not sure of which one is better.  I think the second one should be.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @MatWebb,

 

This calculated field formula will work

 

=IF(DATEDIFF(CALCULATE(MIN(DateTable[firstDayOfMonth]),ALLSELECTED(DateTable[firstDayOfMonth])),MAX(DateTable[firstDayOfMonth]),MONTH)<=1,BLANK(),if(HASONEVALUE(DateTable[firstDayOfMonth]),if(ISBLANK([m_Calc_NumberShips_Export]),BLANK(),AVERAGEX(CALCULATETABLE(SUMMARIZE(DateTable,DateTable[firstDayOfMonth],"ABCD",[m_Calc_NumberShips_Export]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-2),MAX(DateTable[Date]))),[ABCD])),BLANK()))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.