Forum Discussion
Moving Average only on working days
Microsoft EmployeeHi 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
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 :-)