cancel
Showing results for
Did you mean:
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];
LASTDATE('Dashboard'[Transaction Date])
)
);3)

3 REPLIES 3
Frequent Visitor

## Re: Moving Average only on working days

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.

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

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

## Re: Moving Average only on working days

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

Announcements

#### Win Power BI Swag with Community Kudopalooza!

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 133 members 1,909 guests
Recent signins: