06-20-2017 06:40 AM
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(
SUM( Dashboard'[QTY OR]);
06-20-2017 08:06 AM
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.
06-21-2017 12:13 AM
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
06-28-2017 01:41 AM
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 :-)