8 Month Fixed Mean Period

DAX DAX Calculations
1

Hi,

We are tracking vehicle assets and when they have In Service Defects.

I’ve been asked to create a fixed Mean of In Service Defects over an 8 month period, so it would start in January 2020 up to and inclusive of August 2020. This 8 month Mean would then be used to analyse performance over the next 8 month period. I would also have an upper limit (UPL) and a lower limit (LPL)

So the Mean, UPL & LPL are static over each 8 month period.

I would also be creating these for each Asset/Fleet type, in the sample data I’ve created a Fleet Type of AA and BB.

Once the 8 Month Period has ended it would recalculate based on the previous 8 Month period. Really unsure how to create a static 8 month period which recalculates for the next 8 months.

I’ve included a sample data set, a sample PBIX and also sample excel data which populates the data for the below graph and hopefully adds some context around the calculations.

As always, any help or guidance is greatly appreciated and apologies if I’ve been a bit unclear in what I’m asking for or if I’m asking for a bit too much help on this. I’m a bit stumped.

Thanks

David

image
image899×427 24.7 KB

SPC PBIX Data.xlsx (22.1 KB)

SPC File.pbix (138.5 KB)

Test Data SPC.xlsx (19.6 KB)

1 Like
2

Hi @DavieJoe,

See if this gets you started.
I’ve added a grouping column to your date table, with this M code.

Add8MndPeriod = Table.AddColumn( Source,  "8 month fixed period", 
    each Number.RoundDown(
      (
        ((12 * Date.Year([Date])) + Date.Month([Date])) -
        ((12 * Date.Year(Date.From(StartOfYear))) + Date.Month(Date.From(StartOfYear)))
      ) / 8
      , 0
    ), type number
  )

.
Next I created measures based on the logic in your Excel file.

Mean = 
AVERAGEX(
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Date_Table, Date_Table[Month & Year], Date_Table[MonthnYear], Date_Table[8 month fixed period] ),
            REMOVEFILTERS( Date_Table ),
            VALUES( Date_Table[8 month fixed period] )
        ),
        "@Value", [Fleet Type AA]
    ),
    [@Value]
)

.

Xbar = 
AVERAGEX(
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Date_Table, Date_Table[Month & Year], Date_Table[MonthnYear], Date_Table[8 month fixed period] ),
            REMOVEFILTERS( Date_Table ),
            VALUES( Date_Table[8 month fixed period] )
        ),
        "@Value", ABS( [Fleet Type AA] - [Mean] )
    ),
    [@Value]
)

.

UPL = 
AVERAGEX(
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Date_Table, Date_Table[Month & Year], Date_Table[MonthnYear], Date_Table[8 month fixed period] ),
            REMOVEFILTERS( Date_Table ),
            VALUEs( Date_Table[8 month fixed period] )
        ),
        "@Value", [Mean] + ( 2.66 * [Xbar] )
    ),
    [@Value]
)

.

LPL = 
AVERAGEX(
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Date_Table, Date_Table[Month & Year], Date_Table[MonthnYear], Date_Table[8 month fixed period] ),
            REMOVEFILTERS( Date_Table ),
            VALUES( Date_Table[8 month fixed period] )
        ),
        "@Value", [Mean] - ( 2.66 * [Xbar] )
    ),
    [@Value]
)

.

With this result
image
.

Here’s your sample file. SPC File.pbix (157.2 KB)
I hope this is helpful

7 Likes
3

@Melissa thank you, will dig into this tomorrow. Really appreciate this.

4

Hi Melissa,

this is great, gets me really close. One thing I didn’t specify, I could be reading the results wrongly though, is that the Mean, Xbar, UPL & LPL would be using the figures for the previous 8 Month period.

So a new 8 month period would begin in May 2021 and it would show the Mean, Xbar, UPL & LPL figures for 8 Month fixed period 1.

Would this be easy for me to fix?

5

It does exactly what your excel sheet illustrated.

However I would imagine you can quickly fix that, by just using measure branching and CALCULATE to offset the Date_Table[8 month fixed period] by -1. Be aware that this will mean that there won’t be any values for the first period, so you’ll have to deal with that as well.

I hope this is helpful.

2 Likes
6

Hi Melissa,

you’re right, your solution works perfectly. It’s just something that got mentioned to me that there ‘might’ be an offset involved. I can fix this at my end.

Thanks again, really appreciate your help. I’m starting to better understand virtual tables and their applications now.

David

2 Likes
7

I must be having a bad brain day, I can’t seem to be able to offset my calculations by Date_Table[8 month fixed period] by -1.

8

:thinking: Well, we’ll take the long way round then…
Give this a go.

Mean Prev = 
VAR nPeriod = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            VALUES( Date_Table[8 month fixed period] ),
            "@Period", IF( [8 month fixed period] >0, [8 month fixed period]-1)
        ), "@nPeriod", [@Period]
    )
RETURN

AVERAGEX(
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Date_Table, Date_Table[Month & Year], Date_Table[MonthnYear], Date_Table[8 month fixed period] ),
            REMOVEFILTERS( Date_Table ),
            FILTER( VALUES( Date_Table[8 month fixed period] ),
                Date_Table[8 month fixed period] IN nPeriod
            )
        ),
        "@Value", [Fleet Type AA]
    ),
    [@Value]
)

I hope this is helpful.

2 Likes
9

Melissa, you’re only ever helpful. Thank you, much appreciated.

Need to eek out some time for me to improve my DAX!

10

Tested this this morning, works perfectly.

Big thanks again :grinning: :grinning:
David

11

I’m trying to add a further piece of logic into this, where the Measure X-Xbar completes an average of the the Xbar for each period. I’m not sure where I’m going wrong here.

image

SPC File v1.2.pbix (157.8 KB)

12

Hi @DavieJoe,

Just repeat the structure for the other measures as well

Xbar Prev = 
VAR nPeriod = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            VALUES( Date_Table[8 month fixed period] ),
            "@Period", IF( [8 month fixed period] >0, [8 month fixed period]-1)
        ), "@nPeriod", [@Period]
    )
RETURN

AVERAGEX(
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Date_Table, Date_Table[Month & Year], Date_Table[MonthnYear], Date_Table[8 month fixed period] ),
            REMOVEFILTERS( Date_Table ),
            FILTER( VALUES( Date_Table[8 month fixed period] ),
                Date_Table[8 month fixed period] IN nPeriod
            )
        ),
        "@Value", ABS( [Fleet Type AA] - [Mean] )
    ),
    [@Value]
)

.
image

1 Like