moving average working days/non working days

[gooranga1]

I have been trying to get a moving average for deliveries that I am able to select either Working Days or Non working  or Both. It's a bit tricky to explain but I would like people to be able to select a filter on the Working Days and it would show the moving average. I can get it to work for each one individually but when they select both I am getting caught out by the filter context on the graph axis so the moving average is for thet work day type. See error below.

incorrect

this what the above should look like.

correct

The 3 calculations I have are below for each scenario and a "broken" one that gives the incorrect measure. And I was trying to work out how I can display the appropriate measure when someone selects the below slicer.

I thought I could do it on counting the values on the filter but of course that doesn't work in the chart as the filter context will be determined on the graph axis below. So filtering on the work day gives a the moving average for work day and not work day at that point regardless.

Deliveries 84 Day Avg New = 
VAR NumOfDays = 84
VAR LastCurrentDate =
    CALCULATE (
        MAX ( 'DimDate'[Date] ),
        FILTER ( DimDate, DimDate[FutureDay] = 0 )
    )
VAR DaysToAverageAll =
    TOPN (
        NumOfDays,
        CALCULATETABLE ( DimDate, DimDate[Date] <= LastCurrentDate, ALL ( DimDate ) ),
        DimDate[Date], DESC
    )
VAR DaysToAverageWorkDay =
    TOPN (
        NumOfDays,
        CALCULATETABLE (
            DimDate,
            DimDate[Date] <= LastCurrentDate,
            FILTER ( ALL ( DimDate ), DimDate[WorkdayMonSatUK] = "Work Day" ),
            ALL ( DimDate )
        ),
        DimDate[Date], DESC
    )
VAR DaysToAverageNonWorkDay =
    TOPN (
        NumOfDays,
        CALCULATETABLE (
            DimDate,
            DimDate[Date] <= LastCurrentDate,
            FILTER ( ALL ( DimDate ), DimDate[WorkdayMonSatUK] = "Non Work Day" ),
            ALL ( DimDate )
        ),
        DimDate[Date], DESC
    )
    VAR DaysToAverageBroken =
    TOPN (
        NumOfDays,
        CALCULATETABLE (
            DimDate,
            DimDate[Date] <= LastCurrentDate,
            ALLEXCEPT(DimDate,DimDate[Date]),
            values(DimDate[WorkdayMonSatUK]),
            ALL ( DimDate )
        ),
        DimDate[Date], DESC
    )
VAR AvgResultAll =
    DIVIDE (
        CALCULATE ( FactOpsVolumeDC[Total Ops Deliveries], DaysToAverageAll ),
        COUNTROWS ( DaysToAverageAll )
    )
VAR AvgResultWorkDay =
    DIVIDE (
        CALCULATE ( FactOpsVolumeDC[Total Ops Deliveries], DaysToAverageWorkDay ),
        COUNTROWS ( DaysToAverageWorkDay )
    )
VAR AvgResultNonWorkDay =
    DIVIDE (
        CALCULATE ( FactOpsVolumeDC[Total Ops Deliveries], DaysToAverageNonWorkDay ),
        COUNTROWS ( DaysToAverageNonWorkDay )
    )

    VAR AvgResultBroken =
    DIVIDE (
        CALCULATE ( FactOpsVolumeDC[Total Ops Deliveries], DaysToAverageBroken ),
        COUNTROWS ( DaysToAverageBroken)
    )
RETURN
    AvgResultBroken 

 I could do a horrible workaround possibly with book marks and separate measures but I was trying to do it in one measure really but can't work out how to do the filtering?

Not sure if this is clear enough but maybe someone can help.

[gooranga1 · Accepted solution]

In the end I had to do a botch to get it working.

I created a new table to use as Filter on the page. I then removed the actual date dimension working day filter on the page.

Day Type = 
DATATABLE (
    "WorkDayMonSatUK", INTEGER,
    "WorkDayMonSatUKDesc", STRING,
    
    {
        { 1, "WorkDay" },
        { 2, "NonWorkDay" }
       
    }
)

I then used this to decide which measure to show.

Deliveries 84 Day Avg New = 
VAR NumOfDays = 84
VAR DateType =
    MAX ( 'Day Type'[WorkDayMonSatUK] )
VAR CountDateType =
    COUNT ( 'Day Type'[WorkDayMonSatUK] )
VAR LastCurrentDate =
    CALCULATE (
        MAX ( 'DimDate'[Date] ),
        FILTER ( DimDate, DimDate[FutureDay] = 0 )
    )
VAR DaysToAverageAll =
    TOPN (
        NumOfDays,
        CALCULATETABLE ( DimDate, DimDate[Date] <= LastCurrentDate, ALL ( DimDate ) ),
        DimDate[Date], DESC
    )
VAR DaysToAverageWorkDay =
    TOPN (
        NumOfDays,
        CALCULATETABLE (
            DimDate,
            DimDate[Date] <= LastCurrentDate,
            FILTER ( ALL ( DimDate ), DimDate[WorkdayMonSatUK] = "Work Day" ),
            ALL ( DimDate )
        ),
        DimDate[Date], DESC
    )
VAR DaysToAverageNonWorkDay =
    TOPN (
        NumOfDays,
        CALCULATETABLE (
            DimDate,
            DimDate[Date] <= LastCurrentDate,
            FILTER ( ALL ( DimDate ), DimDate[WorkdayMonSatUK] = "Non Work Day" ),
            ALL ( DimDate )
        ),
        DimDate[Date], DESC
    )
VAR AvgResultAll =
    DIVIDE (
        CALCULATE ( FactOpsVolumeDC[Total Ops Deliveries], DaysToAverageAll ),
        COUNTROWS ( DaysToAverageAll )
    )
VAR AvgResultWorkDay =
    DIVIDE (
        CALCULATE ( FactOpsVolumeDC[Total Ops Deliveries], DaysToAverageWorkDay ),
        COUNTROWS ( DaysToAverageWorkDay )
    )
VAR AvgResultNonWorkDay =
    DIVIDE (
        CALCULATE ( FactOpsVolumeDC[Total Ops Deliveries], DaysToAverageNonWorkDay ),
        COUNTROWS ( DaysToAverageNonWorkDay )
    )
RETURN
    IF (
        CountDateType > 1,
        AvgResultAll,
        SWITCH (
            DateType,
            1, AvgResultWorkDay,
            2, AvgResultNonWorkDay,
            AvgResultWorkDay
        )
    )

It does what I need it to do but I feel there must have been a better way to do it than this.

***************************************************************************

************Update 2021-12-29******************************************

So the workaround did work but it had an annoying problem. During each week we have in effect 6 working days, Mon-Sat. Each of these days is more or less the same volume each day except Monday which is always about 75% of the other 5 days. Using the above calculation gave a slighty odd moving average when looking at working days only as it was capturing the last 28 working days sometimes these contained 4 Mondays other times 3 which gave the graph below. 

I wanted a smooth line so I have changed the formula a bit to always take the last 28 days. And then within that  timeframe only take the Working Days. This gives a smoother average line as we are averaging for each 7 days period inluding the same amount of lower value Monday figures.

Deliveries 28 Day Avg New 1 = 
VAR NumOfDays = 28
VAR FirstDatePeriod1 =
    DATEADD ( DimDate[Date], NumOfDays * -1, DAY )
VAR NumWorkDays1 =
    CALCULATE (
        COUNTROWS ( DimDate ),
        FILTER (
            ALL ( DimDate ),
            DimDate[Date] <= MAX ( DimDate[Date] )
                && DimDate[Date] > FirstDatePeriod1
                && DimDate[WorkdayMonSatUK] = "Work Day"
        )
    )
VAR NumNonWorkDays1 =
    CALCULATE (
        COUNTROWS ( DimDate ),
        FILTER (
            ALL ( DimDate ),
            DimDate[Date] <= MAX ( DimDate[Date] )
                && DimDate[Date] > FirstDatePeriod1
                && DimDate[WorkdayMonSatUK] = "Non Work Day"
        )
    )
VAR DateType =
    MAX ( 'Day Type'[WorkDayMonSatUK] )
VAR CountDateType =
    COUNT ( 'Day Type'[WorkDayMonSatUK] )
VAR LastCurrentDate =
    CALCULATE (
        MAX ( 'DimDate'[Date] ),
        FILTER ( DimDate, DimDate[FutureDay] = 0 )
    )
VAR AvgResultAll =
    CALCULATE (
        FactOpsVolumeDC[Total Ops Deliveries],
        DATESINPERIOD ( 'DimDate'[Date], MAX ( DimDate[Date] ), NumOfDays * -1, DAY )
    ) / NumOfDays
VAR AvgResultWorkDay =
    CALCULATE (
        FactOpsVolumeDC[Total Ops Deliveries],
        DATESINPERIOD ( 'DimDate'[Date], MAX ( DimDate[Date] ), NumOfDays * -1, DAY ),
        FILTER ( ALL ( DimDate ), DimDate[WorkdayMonSatUK] = "Work Day" )
    ) / NumWorkDays1
VAR AvgResultNonWorkDay =
    CALCULATE (
        FactOpsVolumeDC[Total Ops Deliveries],
        DATESINPERIOD ( 'DimDate'[Date], MAX ( DimDate[Date] ), NumOfDays * -1, DAY ),
        FILTER ( ALL ( DimDate ), DimDate[WorkdayMonSatUK] = "Non Work Day" )
    ) / NumNonWorkDays1
RETURN
    IF (
        CountDateType > 1,
        AvgResultAll,
        SWITCH (
            DateType,
            1, AvgResultWorkDay,
            2, AvgResultNonWorkDay,
            AvgResultWorkDay
        )
    )

So that gives a much smoother average line

[v-shex-msft]

Hi @gooranga1,

Thanks for sharing the solution with your scenario, I think they will help if others have similar requirements.😊

Regards,

Xiaoxin Sheng