Trouble with date comparison

[msimmonsmcse]

I am trying to write a measure that will calculate the number of business days in a given period of time. I have a date table that starts at 1/1/2019 and ends on 12/31/2020. Here is my measure:

Count of Business Days =

var StartDate = MIN('Date'[Date])

var EndDate = IF(MAX('Date'[Date])>TODAY()-1, TODAY()-1, MAX('Date'[Date])

RETURN

StartDate will return: 1/1/2019

EndDate returns: 44117 (sets data type to Decimal).

If I change the data type to Date, I get: dddd MMMM d yyyy

Why is it not returning the value of TODAY()-1? If I set the variable to only return TODAY()-1, it returns yesterdays date correctly

[amitchandak]

@msimmonsmcse , Create a column work day in you table

Work Date = if(WEEKDAY([Date],2)>=6,0,1)

If you are using slicer on the date of date table

Sum(Date[Work Date]) will give you that.

Refer 2nd page of this file how to use work day diff between two tables

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

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[VNRaju]

Try

var EndDate = FORMAT (IF(MAX('Date'[Date])>TODAY()-1, TODAY()-1, MAX('Date'[Date]),"mm/dd/yyyy" )

Good luck!