countrows return less row with Max() function

[Dunner2020]

Hi there,

I have created a measure which calculates the working days between two dates and excludes public holidays. Here is the measure:

No of days between two events = 

Var start_date = MAX('Table'[startdate])

Var end_date = MAX('Table'[Enddate])

Var varDaycount =

CALCULATE( COUNTROWS(Dates),

FILTER(ALL(Dates), Dates[Date] >= start_date &&

Dates[Date] <=end_date &&

Dates[IsHoliday] = FALSE() &&

(Dates[Day Of Week Name] <> "Saturday" &&

Dates[Day Of Week Name] <> "Sunday")

)

)

Return

varDaycount

I have noticed that when startdate is "12/08/2020 12:00:00 PM" and enddate is "31/08/2020 8:34:00 AM", measure returns 13 days instead of 14 days (there is no public holidays between two dates). I started debugging the program. I created another debugging measure which looks like as follow:

Debug measure = 

Var end_date = DATEVALUE("31/08/2020 8:34:00 AM" )

Var start_date = DATEVALUE("12/08/2020 12:00:00 PM")

RETURN

Var varDaycount =

CALCULATE(COUNTROWS(Dates),

FILTER(ALL(Dates), Dates[Date] >= start_date &&

Dates[Date] <=end_date &&

Dates[IsHoliday] = FALSE() &&

(Dates[Day Of Week Name] <> "Saturday" &&

Dates[Day Of Week Name] <> "Sunday")

)

)

Return

varDaycount

Debugging measure returns 14 days. I am not sure where I am making the mistake. Any advice would be really appreciated.

The sample file can be downloaded from here 

[sanalytics · Accepted solution]

@Dunner2020 

It seems that your No of days between two events is taking 1 day after your start date..
why cant you wrap the start date and end date with datevalue and see what you are getting??
I mean to say,

No of days between two events = 

Var start_date = Datevalue(MAX('Table'[startdate]))

Var end_date = Datevalue(MAX('Table'[Enddate]))

Var varDaycount =

CALCULATE( COUNTROWS(Dates),

FILTER(ALL(Dates), Dates[Date] >= start_date &&

Dates[Date] <=end_date &&

Dates[IsHoliday] = FALSE() &&

(Dates[Day Of Week Name] <> "Saturday" &&

Dates[Day Of Week Name] <> "Sunday")

)

)

Return

varDaycount

Check what happens and let us know..

Regards,

sanalytics

if it is your solution then please accept it as  solution and also please like