Error in start and end date blank values

[Anonymous]

Hello, 

I'm trying to find the time in days between two dates (Start date & End Date) as some fields can be blanks, I'm using the following measure: 

Analysis Time (bussiness days) =

SWITCH

( TRUE(),Issues[Start Date]=BLANK()||Issues[End Date]=BLANK(),

COUNTROWS (

FILTER (

ADDCOLUMNS( CALENDAR ( Issues[Start Date].[Date],Issues[End Date].[Date]),"Day of Week", WEEKDAY( [Date], 1 ) ),

[Day of Week] <> 6

&& [Day of Week] <> 7

)

))

However, the results show an error 

[Fowmy]

@Anonymous 

Can you try the following measure?

Analysis Time (bussiness days) = 
var __start =  MAX(Data[Start Date])
var __end =  MAX(Data[End Date])

return

IF(
    __start=BLANK() || __end=BLANK(),BLANK(),
    COUNTROWS(
        FILTER(
            CALENDAR(__start,__end),
            WEEKDAY([Date],1) < 6
        )
    )
)

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@amitchandak i tried however the same error shows again. 

[amitchandak]

@Anonymous , I tried same formula (as a new column) on my file having enddate null and it worked

Business days = SWITCH(TRUE(), NOT(ISBLANK(Employee[Start Date])) && NOT(ISBLANK(Employee[End Date])) , COUNTROWS(FILTER(
ADDCOLUMNS(CALENDAR([Start Date],[End Date]),"Weekday",WEEKDAY([Date])),[Weekday]
<6)))

[Anonymous]

@amitchandak  I'm using Start dat calculated column as :

Start Date = CALCULATE(FIRSTNONBLANK(Tracker[New Value Start],1),FILTER(Tracker,Issues[Key]=Tracker[Key]&&Tracker[Old Value]="BA Team"))

and End date column as : 

End Date = CALCULATE(FIRSTNONBLANK(Tracker[New Value Start],1),FILTER(Tracker,Issues[Key]=Tracker[Key]&&Tracker[Field]="status"&&Tracker[New Value]="Done"))

could that have any effect on the results even though the data type of both columns are the same > Date/time

[amitchandak]

@Anonymous , Are these columns of measures. Hope they are columns.

Is this column a date [New Value Start]?

Also do not use ".Date", unless the column has timestamp

[Anonymous]

@amitchandak yes the columns are created of measures, also [New Value Start] is a date column. 

[amitchandak]

@Anonymous , it should be

not(isblank(Issues[Start Date]))  && not(isblank(Issues[End Date])) ,

Then take a diff

for working diff refer to my file

https://www.dropbox.com/s/o2v5ekfiw5057do/Working%20date%20between%20Dates%20Measure%20and%20Table.p...

or 2nd page of this file

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Or refer this blog

https://curbal.com/blog/glossary/networkdays-dax