Reply
Frequent Visitor
Posts: 4
Registered: ‎06-19-2017

Moving Average only on working days

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)

 

 

 

Frequent Visitor
Posts: 4
Registered: ‎06-19-2017

Re: Moving Average only on working days

Sketch.png

 

 

 

 

 

 

 

 

 

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.

Community Support Team
Posts: 5,709
Registered: ‎09-21-2016

Re: Moving Average only on working days

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 )
)

2.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 4
Registered: ‎06-19-2017

Re: Moving Average only on working days

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 :-)