Part 2 - 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'. 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%...

WIth help from a previous post (https://community.powerbi.com/t5/Desktop/DAX-formula-for-Calculated-Column-Lead-in-time-Remaining-da...), 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.    

[Anonymous · Accepted solution]

@minishshah 
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.

[minishshah]

Paul,

YOU ARE A GENIOUS! 

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

Please stay safe.

[Anonymous]

@minishshah 

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.1	12	12

Regards
Paul

[minishshah]

@Anonymous ,

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

[minishshah]

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.

[minishshah]

The issue is not resolved. Please assist