Moving Average only on working days

[victorinox]

Hey,

I have question concerning moving average.

How do you make sure you only making a moving average on working days (monday - friday), so saturday & sunday can not be taken into account. Currently I get moving average as well on saturday and sunday even if I do not have any data on these days. Seems problem with the firstdate

OR Moving_Average_3_days = DIVIDE(
CALCULATE(
SUM( Dashboard'[QTY OR]);
DATESBETWEEN(
'Dashboard'[Transaction Date];
FIRSTDATE(DATEADD('Dashboard'[Transaction Date];-3;DAY));
LASTDATE('Dashboard'[Transaction Date])
)
);3)

[v-yulgu-msft]

Hi @victorinox,

You can create a calander date table, add a calculated column in this date table. There is no need to add a relationship between date table and dashboard table.

Date table =
CALENDAR ( DATE ( 2017, 6, 1 ), DATE ( 2017, 6, 30 ) )

Date2 =
IF (
    WEEKDAY ( 'Date table'[Date], 2 ) = 6,
    DATEADD ( 'Date table'[Date].[Date], -1, DAY ),
    IF (
        WEEKDAY ( 'Date table'[Date], 2 ) = 7,
        DATEADD ( 'Date table'[Date].[Date], -2, DAY ),
        'Date table'[Date]
    )
)

Then, in dashboard table, use below formula to generate the date 3 days ago. 

Earliest first date =
LOOKUPVALUE (
    'Date table'[Date2],
    'Date table'[Date], DATEADD ( Dashboard[Transaction Date].[Date], -3, DAY )
)

By the way, if above result meets your requirement, I think it is not available to achieve the logic without IF function. As for '6/19/2017(Monday)', you need to return '6/16/2017(Friday)', while for Tuesday and Wensday, you also need to return Friday, the logic is not common. However, if you need to return '6/14/2017(Wensday)' for '6/19/2017(Monday)', I think we can find a common logic to achieve your goal without using IF.

Best regards,
Yuliana Gu

[victorinox]

hi @v-yulgu-msft

Thanks for the proposal, good first step. But public holidays are still not filtered out.

I believe that working with an index would work, I have a list of all the workingdays in my ERP system. If I would add an index to every day, I could use this as a starting point.

Still stuck whether I can automatically add an index to each row, or whether I shoudl do it manually 😕

Don't know yet how moving average formula would loko like 😕

Any help is welcome 🙂

[victorinox]

I added an example. For Tuesday he does not find any earliest first date, because this would be saturday.
In fact report should take Tuesday in such a case. For Wednesday first date would be sunday, but this is not in data set, report should take Friday.

Can this logic be made without IF formula, because this will make it very complex assuming the holidays as well.