Calculate number of dates between 2 dates if one field is left blank

[Lihobday]

I have a problem that require some help. Really need some experts advice here... thank you

I have a requirement... I have 3 dates. First is "Assigned date" and second is "Actual briefing date"and third is "Close date".

1. SOLVED - See fomulae below - I can calculate by working days the difference between 'actual brefing date' and 'assigned date'. Sometimes 'assigned date' field is left blank and i will still need to calculate the difference of 'actual briefing date' till 'today's date'.

2. I have a column 'Close Date' that has been addednow. How may I calculate by working days the difference between 'Close date' and 'Actural Briefing date'. The challenge is sometimes 'actual briefing date' field will be left blank. And i will get the error from POWERBI "The start date or end date in Calendar Function can not be Blank value"

Is it possible to have a fomulae that calculates only if there is an entry in "Actual Briefing date" else it will not calculate? I did try to use the filter in BI but that would not work if the fomulae is being used. 

Really need some experts advice here on the correct formulae to use for No 2 above ... thank you

Thank you!!

Fomulae used for No 1. above  -  WorkDay Count = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Assigned date], IF ( [Actual briefing date] <> BLANK (), [Actual briefing date], TODAY () ) ), "DayofWeek", WEEKDAY ( [Date], 1 ) ), [DayofWeek] <> 1 && [DayofWeek] <> 7 ) )

[Lihobday]

I have a problem that require some help. Really need some experts advice here... thank you

I have a requirement... I have 3 dates. First is "Assigned date" and second is "Actual briefing date"and third is "Close date".

1. SOLVED - See fomulae below - I can calculate by working days the difference between 'actual brefing date' and 'assigned date'. Sometimes 'assigned date' field is left blank and i will still need to calculate the difference of 'actual briefing date' till 'today's date'.

2. I have a column 'Close Date' that has been addednow. How may I calculate by working days the difference between 'Close date' and 'Actural Briefing date'. The challenge is sometimes 'actual briefing date' field will be left blank. And i will get the error from POWERBI "The start date or end date in Calendar Function can not be Blank value"

Is it possible to have a fomulae that calculates only if there is an entry in "Actual Briefing date" else it will not calculate? I did try to use the filter in BI but that would not work if the fomulae is being used. 

Really need some experts advice here on the correct formulae to use for No 2 above ... thank you

Thank you!!

Fomulae used for No 1. above  -  WorkDay Count = COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Assigned date], IF ( [Actual briefing date] <> BLANK (), [Actual briefing date], TODAY () ) ), "DayofWeek", WEEKDAY ( [Date], 1 ) ), [DayofWeek] <> 1 && [DayofWeek] <> 7 ) )

[Anonymous]

I would propose using variables, which you can check against first and then feed an always correct date into your formula.

An example might be:

Your Measure = VAR YourDate = if(IsBlank([Close Date]), TODAY(), [Close Date])
RETURN
<Your formula here>

You can then use the YourDate measure inside whatever formula you like and will know its never blank.

[Lihobday]

Thanks. I tried using your fomulae. It still gave me an error when there is no value in Role brief date. " The start date or end date in Calendar function can not be Blank value." 

Your Measure = VAR YourDate = if(IsBlank([Role Close Date]), TODAY(), [Role Close Date])
RETURN
COUNTROWS ( FILTER ( ADDCOLUMNS ( CALENDAR ( [Role Brief Date], IF ( [Role Close Date] <> BLANK (), [Role Close Date], TODAY () ) ), "DayofWeek", WEEKDAY ( [Date], 1 ) ), [DayofWeek] <> 1 && [DayofWeek] <> 7 ) )

[v-huizhn-msft · Accepted solution]

Hi @Lihobday,

After review, I think the solution @Anonymous posted is reasonable. Please mark it as answer if you agree with me. So that more members will get useful/helpful information from here.

Best Regards,
Angelia

[Anonymous]

Looking at your formula i notice 2 things:

• You haven't use the variable we created inside your calendar function.
• You are also using [Role Brief Date], can you guarentee this is never blank either?  If so, we just need to create a 2nd variable

Here is what using the variable in your code would look like.  I've not checked your code for correctness, just what it looks like with the variable.

Your Measure = VAR YourDate = if(IsBlank([Role Close Date]), TODAY(), [Role Close Date])
RETURN
COUNTROWS( 
	FILTER(
		ADDCOLUMNS(
			CALENDAR(
				[Role Brief Date], 
				YourDate
			), 
			"DayofWeek", 
			WEEKDAY(
				[Date],
				1
			)
		), 
		[DayofWeek] <> 1 && [DayofWeek] <> 7
	)
)

[Lihobday]

Thank you Ross. This works!

[Anonymous]

What immediately jumps out at me is that you didn't use the variable recreated, although you did make use of the IF statement.  The second think i notice is that you are using [Role Brief Date].  Will this never be blank?  If it can be, you might need to create a second variable.

Here is what your code would look like, if you used the variable inside your calendar function  (note i've not checked your formula for correctness, only the variables use):

Your Measure = VAR YourDate = if(IsBlank([Role Close Date]), TODAY(), [Role Close Date])
RETURN
COUNTROWS( 
	FILTER(
		ADDCOLUMNS(
			CALENDAR(
				[Role Brief Date], 
				YourDate
			), 
			"DayofWeek", 
			WEEKDAY(
				[Date],
				1
			)
		), 
		[DayofWeek] <> 1 && [DayofWeek] <> 7
	)
)

[Anonymous]

The immediate thing that strikes me is that you didn't use the variable you created inside your calendar operation.  Replace your IF statement with the 'YourDate' variable.

This is what it would look like.  I've not checked if your formula is correct, just that this was the purpose of using the variable

Your Measure = VAR YourDate = if(IsBlank([Role Close Date]), TODAY(), [Role Close Date])
RETURN
COUNTROWS( 
	FILTER(
		ADDCOLUMNS(
			CALENDAR(
				[Role Brief Date], 
				YourDate
			), 
			"DayofWeek", 
			WEEKDAY(
				[Date],
				1
			)
		), 
		[DayofWeek] <> 1 && [DayofWeek] <> 7
	)
)