cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
victorinox Frequent Visitor
Frequent Visitor

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)

 

 

 

3 REPLIES 3
victorinox Frequent Visitor
Frequent Visitor

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.

v-yulgu-msft Super Contributor
Super Contributor

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.
victorinox Frequent Visitor
Frequent Visitor

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 133 members 1,909 guests
Please welcome our newest community members: