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 it is invoiced and shipped.

 

For convenience, i have placed a file and shared under my dropbox account. here is the link: https://www.dropbox.com/s/n0gumcr5wqy9aik/DAX%20formula%20for%20Calculated%20Column%20-%20Lead%20in%20time%20Remaining%20days%20to%20order2%20%20%281%29.pbix?dl=0

 

WIth help from a previous post (https://community.powerbi.com/t5/Desktop/DAX-formula-for-Calculated-Column-Lead-in-time-Remaining-days-to/m-p/1042461#M489304), I have only been able to resolve this issue partially. However, the logic that is still missing is that once the calendar reaches new month (i.e. 07/01/2020), the counter for the "RemainingDaysToOrder" column should start with number located in the column next door to it called "Column" (i.e. 12) and continue counting down like it has so until "RemainingDaysToOrder" column reaches 1. Further, once that column reaches 1, then it remains 1 until it reaches dates of a new month (i.e. 08/01/2020), which is when it will again grab the corresponding number located in the column next door called "Column" (i.e. 10) and then it count downs again.

 

Any help with this would be greatly appreciated.    

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.

6 Replies

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous ,

       

      Hello, I am still needing assistance with this issue. Please assist.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      Paul,

       

      YOU ARE A GENIOUS! 

       

      That worked! Thank you so much for your assistance. Greatly Appreciated, my friend!  

       

      Please stay safe.