DAX DATEDIFF Working Days and Others

[markefrody]

Hi, 

I am trying to get the date difference between Load Date and Plan Date but I need to exclude Saturdays and Sundays. Also if the Plan Date is blank, I need today's date + 1 day to replace the blank Plan Date and from there compute the date difference.

Can this be done in DAX? Appreciate if you can help me to create DAX formula as per my above requirements. Thanks!

Best regards,

Mark Visda

[v-cherch-msft]

Hi @markefrody 

You may create a calendar table and use below measure.Attached the sample file for reference.

Measure = 
CALCULATE (
    COUNT ( 'Calendar'[Date] ),
    FILTER (
        GENERATE ( Table1, FILTER ( 'Calendar', 'Calendar'[ISWeekum] = 0 ) ),
        Table1[Load Date] < 'Calendar'[Date]
            && Table1[Plan Date2] >= 'Calendar'[Date]
    )
)

Regards,

Cherie

[markefrody]

Hi @v-cherch-msft (Cherie),

Thank you for sending your suggestion. Unfortunately I cannot creat a calendar table since I am using a Direct Query for storage method. Is there any other way to do this without adding a calendar table?

Best regards,

Mark V

[v-cherch-msft]

Hi @markefrody 

Try below measure:

Measure 2 = var _Calendar=ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2019,3,1)),"ISWeekum",IF(WEEKDAY([Date],1)>=6,1,0))
return CALCULATE (
    COUNTROWS(
    FILTER (
        GENERATE (Table1,FILTER(_Calendar,[ISWeekum]=0) ),
        Table1[Load Date] < [Date]
            && Table1[Plan Date2] >= [Date]
    )
)
)

Regards,

Cherie

[Greg_Deckler]

Have a look at this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

[markefrody]

Hi @Greg_Deckler. Thank you for sending your suggestion. I have tried to do the measure:

But I am getting this error:

Is it because I have blank data in some of my dates?