Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous 

 

I have looked into the pbix. I see the logic you want but not clear with your expected result. Just to be sure, did you mean you want the 'Remaining days to order' column like:

 

Date column Remaining days to order
2020.2.1 9 9
2020.2.2 8 8
2020.2.3 7 7
... ... ...
.. 1 1
... 0 1
...... 1
2020.3.11212

 

Regards
Paul

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    The issue is not resolved. Please assist