DAX measure to calculate a rolling average of the lead times of the last 3 purchases

[Anonymous]

Hi Folks, 

Long time lerker, first time poster 🙂

Really hoping someone can assist/guide me a little.

I’m trying to draw a chart that demonstrates a trend line of a rolling averages of lead times for items that are sporadically purchased.

The chart will be shown in a drill through report for specific items thus will only be displayed for single items at a time. The main report already shows how many items have increased/decreased lead times when selecting two time periods, the user then selects and item to drill into and find out further granular details.

I need to implement the formula as a measure as the end user will be able to filter by dates, supplier, purchase location and more.

I'd also like to have the ability to look at a supplier's lead time (Regardless of item) in another report and would expect this formula could potentially assist here.
I’ve utilised the following formula at this stage (found here) but it doesn’t correctly ignore the row context to draw a trend chart.

Lead time past 3 orders =

VAR thisdate =

    MAX ( 'Supply Lead Time'[Order Date] )

VAR last3leadtimes =

    TOPN (3, FILTER ( ALL ( 'Supply Lead Time'[Order Date] ), 'Supply Lead Time'[Order Date] <= thisdate ), 'Supply Lead Time'[Order Date], DESC )

RETURN

    CALCULATE ( AVERAGE( 'Supply Lead Time'[Lead time] ), last3leadtimes )

I spent a couple of hours testing and trying to make it work the way I described through adding in additional filter expressions to no avail.

The Model we have is a star schema with a fact table of lead times (Millions of Purchase orders) and a number of dimension tables including:
- Supplier (Thousands)
- Item details (~ A hundred thousand of unique items)
- Location (Hundreds),
- Date (5 years)

I’ve attached a PBIX as a basic example of data and an excel file outlining how I’d like it to calculate and chart.   

[OwenAuger]

You're welcome, and glad it's working 🙂

Ah right - good point. I would generally suggest not including unaggregated numerical columns from your fact table in a visual.

The strict rule some follow to avoid this sort of issue is:

Hide all fact table columns and never include them in visuals. Explicit measures (i.e. written with DAX) must be used to display any value based on columns of a fact table. All columns used for grouping or filtering must be in separate dimension tables.

To explain further:

When you include a column in the visual set to "do not summarize" (as opposed to an implicit/explicit measure with SUM/AVERAGE etc), this becomes an additional filter. By including "Lead time" this way, each row of the table includes that specific Lead time value as a filter, and this will interfere with the calculation.

For example, on the last row of the screenshot where Item Code = A, the filters are:

• Item Code = A
• Order Date = 2021-04-01
• Lead time = 4

In calculating "Lead time past 3 orders", we want to keep Item Code, we override Order Date, but if Lead time = 4 remains as a filter, we would only include the past 3 orders where Lead time = 4.

The solution is to use a measure if we want to display Lead time, such as Average Lead time.

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @Anonymous 

The immediate issue is due to this expression:

FILTER (
    ALL ( 'Supply Lead Time'[Order Date] ),
    'Supply Lead Time'[Order Date] <= thisdate
)

This will return all Order Dates on or before thisdate, ignoring any other filters such as Item Code. This is because when ALL ( ... ) is used to return a table, it all values in the specified column(s) regardless of filters.

Without making any other changes, a measure like this should fix this problem:

Lead time past 3 orders (without date table) = 
VAR thisdate =
    MAX ( 'Supply Lead Time'[Order Date] )
VAR PreviousDates =
    CALCULATETABLE (
        VALUES ( 'Supply Lead Time'[Order Date] ),
        'Supply Lead Time'[Order Date] <= thisdate
    )
VAR last3leadtimes =
    TOPN ( 3, PreviousDates, 'Supply Lead Time'[Order Date], DESC )
RETURN
    CALCULATE ( AVERAGE( 'Supply Lead Time'[Lead time] ), last3leadtimes )

The expression for PreviousDates ensures that the Order Date filter is modified but other filters are retained.

A separate suggestion is to consider adding a Date table to your model, with a relationship to Order Date, and disabling Auto Date/Time under File > Options & Settings > Options > Current File > Data Load > Time intelligence > Auto date/time.

With a Date table, you would need to ensure that you only apply filters on columns of 'Date', not 'Supply Lead Time'[Date].

It is generally preferable to modify Date filters in a Date dimension rather than fact table. I've attached the PBIX containing a measure modified to use a 'Date' table.

Regards,

Owen

[Anonymous]

Hi Owen,

Thanks so much for your detailed response, your answer is perfect and super clear.

In our production model we have a date table, I'll make sure I refer to that in the finalised query as per your excellent examples.

One Query, I had an issue initally when updating my code, i was still getting incorrect responses.
Take a look at the third column (Lead time/average lead time) of the table, when the third column is set to 'dont summarise' I get the wrong value for the rolling average measure in the 4th column.

I came across this by comparing the queries that Power BI is generating in the back end (performance analyser) and noticed mine was different and had more columns in the queries.  
Any insight into why this is the case?
It's also the reson I didn't figure this out myself, I played with the values fuction but was still getting incorrect answers due to the don't summarise on the 'lead time' column.   

Cheers,