Rolling average across a Distinct calculation showing incorrect results

[MatWebb]

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

[Ashish_Mathur]

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.

[MatWebb]

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

[Ashish_Mathur]

Hi,

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

[v-jiascu-msft]

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

[MatWebb]

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

[Ashish_Mathur · 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.

[MatWebb]

@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

[Ashish_Mathur]

Hi,

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

[Ashish_Mathur]

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.