topic Re: Error in start and end date blank values in DAX Commands and Tips

Error in start and end date blank values

Anonymous — Thu, 24 Dec 2020 10:14:57 GMT

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

Re: Error in start and end date blank values

amitchandak — Thu, 24 Dec 2020 11:02:56 GMT

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.pbix?dl=0

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

Re: Error in start and end date blank values

Anonymous — Thu, 24 Dec 2020 11:34:56 GMT

amitchandak i tried however the same error shows again.

Re: Error in start and end date blank values

amitchandak — Thu, 24 Dec 2020 12:05:21 GMT

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)))

Re: Error in start and end date blank values

Anonymous — Thu, 24 Dec 2020 12:33:30 GMT

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

Re: Error in start and end date blank values

amitchandak — Thu, 24 Dec 2020 17:46:27 GMT

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

Re: Error in start and end date blank values

Fowmy — Thu, 24 Dec 2020 20:20:46 GMT

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

Re: Error in start and end date blank values

Anonymous — Sun, 27 Dec 2020 09:59:05 GMT

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