Forum Discussion
Part 2 - DAX formula for Calculated Column - Lead in time/ Remaining days to order
I am trying to create a calculated column to show count for 'Remaining days to order'. The count is based on Total working days minus 10 days lead time from the time order is placed to the time when ...
Anonymous
So sorry for late reply, had busy time.You need to add the month and year filter to the variable b, try this dax:
RemainingDaysToOrder = var a=CALCULATE(MAX('Dates'[IsWorkingDay]),FILTER('Dates','Dates'[Date]=EARLIER(Dates[Date])-1)) var b= CALCULATE(COUNTROWS('Dates'),FILTER('Dates','Dates'[Date]<EARLIER('Dates'[Date])&&'Dates'[IsWorkingDay]=0 && Dates[Date].[Month]=EARLIER(Dates[Date].[Month])&&[Date].[Year]=EARLIER([Date].[Year]))) Return IF('Dates'[IsWorkingDay]=0,BLANK(), IF('Dates'[Column]+b>=1,'Dates'[Column]+b,1))Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, you interpretation below is correct. I would like the "RemainingDaysToOrder" column to behave like this
| Dates | Holiday | IsWorkingDay | Day Type | Column | RemainingDaysToOrder | |||||
| 07/01/2020 | 0 | 1 | Weekday | 11 | 11 | |||||
| 07/02/2020 | 0 | 1 | Weekday | 10 | 10 | |||||
| 07/03/2020 | 1 | 0 | Weekday | 9 | ||||||
| 07/04/2020 | 1 | 0 | Weekend | 8 | ||||||
| 07/05/2020 | 0 | 0 | Weekend | 7 | ||||||
| 07/06/2020 | 0 | 1 | Weekday | 6 | 9 | |||||
| 07/07/2020 | 0 | 1 | Weekday | 5 | 8 | |||||
| 07/08/2020 | 0 | 1 | Weekday | 4 | 7 | |||||
| 07/09/2020 | 0 | 1 | Weekday | 3 | 6 | |||||
| 07/10/2020 | 0 | 1 | Weekday | 2 | 5 | |||||
| 07/11/2020 | 0 | 0 | Weekend | 1 | ||||||
| 07/12/2020 | 0 | 0 | Weekend | 0 | ||||||
| 07/13/2020 | 0 | 1 | Weekday | -1 | 4 | |||||
| 07/14/2020 | 0 | 1 | Weekday | -2 | 3 | |||||
| 07/15/2020 | 0 | 1 | Weekday | -3 | 2 | |||||
| 07/16/2020 | 0 | 1 | Weekday | -4 | 1 | |||||
| 07/17/2020 | 0 | 1 | Weekday | -5 | 1 | |||||
| 07/18/2020 | 0 | 0 | Weekend | -6 | ||||||
| 07/19/2020 | 0 | 0 | Weekend | -7 | ||||||
| 07/20/2020 | 0 | 1 | Weekday | -8 | 1 | |||||
| 07/21/2020 | 0 | 1 | Weekday | -9 | 1 | |||||
| ... | ||||||||||
| 07/29/2020 | 0 | 1 | Weekday | -17 | 1 | |||||
| 07/30/2020 | 0 | 1 | Weekday | -18 | 1 | |||||
| 07/31/2020 | 0 | 1 | Weekday | -19 | 1 | |||||
| 08/01/2020 | 0 | 0 | Weekend | 10 | ||||||
| 08/02/2020 | 0 | 0 | Weekend | 9 | ||||||
| 08/03/2020 | 0 | 1 | Weekday | 8 | 10 | |||||
| 08/04/2020 | 0 | 1 | Weekday | 7 | 9 | |||||
| 08/05/2020 | 0 | 1 | Weekday | 6 | 8 | |||||
| 08/06/2020 | 0 | 1 | Weekday | 5 | 7 | |||||
| ... |
In another words, I would like the "RemainingDaysToOrder" column to stop counting if it is either Holiday or Weekend and then pickup countdown when "IsWorkingDay" column shows 1 or when "Date Type" column shows Weekday.
The issue is not resolved. Please assist