DAX formula for Calculated Column - Lead in time/ Remaining days to order

[minishshah]

I am trying to create a calculated column to show count for 'Remaining days to order' as you see below. 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.

In the screenshot below, you will see the actual formula along with the results in the far right side of the image. The problem i am running into is that it is also counting 'weekend' days as part of the calculation. Hence you can see, after the first three rows, it is placing "1" as i have circled in Red color like it is defined in the formula, but it is also counting 8 and 7 respectively in the background.

what I had hoped to get out of this DAX formula that it would leave the rows with Weekend blank or skip and place the 8,7.. into where it currently displays 6,5 respectively and continue countdown, but it is not behaving correctly.

Please help.

Thank you in Advance.

[v-kelly-msft]

Hi @minishshah ,

Could you pls upload your .pbix file to onedrive business and share the link with us?It's a bit difficult to test the calculation without data...

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[minishshah]

Hi @v-kelly-msft ,

Thank you for replying. i have placed the copy at the following location: https://spgcorp-my.sharepoint.com/:u:/g/personal/minish_shah_spgusa_com/EQO5kdUQBHdCi62mPUXeOQwBQT0u...

Please let me know if you have any trouble opening.

[v-kelly-msft]

Hi @minishshah ,

Sorry I cant open the .pbix file via your link as it needs access to visit it,could you pls upload it to onedrive for business and make a public link share with us?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[minishshah]

Hello @v-kelly-msft ,

I am not familiar with onedrive for business. do you have a specific link where i can post this data? 

For convenience, i have placed a file and shared under my dropbox account. here is the link: https://www.dropbox.com/s/exu2vteazs7ikeq/Daily%20Booking%20Target%20test.pbix?dl=0

[v-kelly-msft]

Hi @minishshah ,

I have modified your measure as below:

RemainingDaysToOrder = 
// IF(
//                         (
//                             (Dates[Count Working Days (Month)]-10) - Dates[DayInMonth]) > 0 && Dates[IsWorkingDay] = 1
//                             , ((Dates[Count Working Days (Month)]-10) - Dates[DayInMonth]), 1
//                         )
var a=(Dates[Count Working Days (Month)]-10) - Dates[DayInMonth]
Return
IF('Dates'[IsWorkingDay]=0,CALCULATE(a,FILTER('Dates','Dates'[Date]=EARLIER(Dates[Date])-1)),IF( Dates[Count Working Days (Month)]-10 - Dates[DayInMonth]> 0 && Dates[IsWorkingDay] = 1,a,1))

And you will see:

If I misunderstood your logic,let me know,I will correct the dax expression.

Here is the related .pbix file.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[minishshah]

Hello @v-kelly-msft ,

Thank you so much for replying back.

I am looking for a slightly different results. I need the measure to skip the row, leave it blank if 'IsWorkingDay' column has 0 and resume counting on the next instance where that column return 1.

For instance,  In the screenshot you posted, i am looking for the measure to leave the rows with the number '8' and '7' blank since those are weekend days, but i want it to resume counting for next 5 working days (IsWorkingDay =1) as 8,7,6,5,4 instead of 6,5,4,3,2 as listed in the screenshot. Then, skip any weekend days (IsWorkingDay=0) and resume counting on the next working days as 3,2,1,1,1 (if the counter reaches 1, then it remains 1 until the calendar begins for the next month. 

Please let me know if i need to clarify further. Thank you so much for your assistance. 

[v-kelly-msft]

Hi @minishshah ，

I write 2 dax expressions for you,as in your original formular,I see,if( 'Dates[counting working days(Month)]-10-'Dates[DayInMonth]<=0,you wanna return 1,so if it is the logic, see below:

1.First create a calculated column as below:

Column = (Dates[Count Working Days (Month)]-10) - Dates[DayInMonth]

2,Then get the result you need:

_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))
Return
IF('Dates'[IsWorkingDay]=0,BLANK(),
IF('Dates'[Count Working Days (Month)]-10-'Dates'[DayInMonth]<=0,1,
'Dates'[Column]+b))

And you will see:

As you see,after 4,there is 1 not 3,as 'Dates[counting working days(Month)]-10-'Dates[DayInMonth]<0.

But in your last post ,I see another logic,that is it will resuming count ,until the result equals 1,then it will remain 1.

If so,pls see below:

1.Create the first column as mentioned above.

2.Then get the result you need:

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))
Return
IF('Dates'[IsWorkingDay]=0,BLANK(),
IF('Dates'[Column]+b>=1,'Dates'[Column]+b,1)
)

And you will see:

 For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[minishshah]

Hi @v-kelly-msft ,

Thank you for replying with the solution. The formula works for the month of April, but it does not for future months after that. I see following two issues with the measures.

1. For the month of May, the 'RemainingDaysToOrder' column starts at 18 (see screenshot below), but rather it should start at "10" like it shows in the 'Column' name column. In another words, For those rows that begin with the new month, the counter should start at the number shown in the corresponding "Column' name column. In this case, May should have started with 10 not 18. The same thing goes for each following months after that, i.e. June, July, August, etc.

Month of May:

Month of June:

2. I also see an issue when i increase the Date Range, for instance i had set my date range from 3/1/2019 - 2/1/2021. Hence, although I see that the number shown for the beginning of the month in the 'Column' name column is correct, the 'RemainingDaysToOrder" accumulates numbers from whatever the start date and end date set in the date range, which in this case was from 3/1/2019 - 2/1/2021. Therefore, I think the same logic applies here which is for those rows that begin with the new month, the counter should start at the number shown in the corresponding "Column' name column.

[minishshah]

Hello, I am still in need of helping with this problem. Please fast.

[minishshah]

Hello All,

I am still need of assistance with this post. I am still struggling with calculation issues mentioned above. 

PLEASE ASSIST.

[minishshah]

I have added two new measures today as follows:

WorkingDaysThisYear = SUMX(FILTER(Dates, 'Dates'[Year] = YEAR(TODAY())), 'Dates'[IsWorkingDay])

Working Days Remaining in Month =

SUMX(FILTER(Dates,

Dates[Date] > TODAY() &&

Dates[Date] <= EOMONTH(TODAY(),0)

),'Key Measures'[WorkingDaysThisYear])

I am trying to figure how i can use this to solve my problem.... any ideas? 

[minishshah]

I am still struggling with this issue.

Any assistance anyone can provide in this would be greatful.