cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markefrody Member
Member

DAX DATEDIFF Working Days and Others

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!

Capture Date.PNG

 

Best regards,

Mark Visda

5 REPLIES 5
Super User
Super User

Re: DAX DATEDIFF Working Days and Others

Have a look at this:

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: DAX DATEDIFF Working Days and Others

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
markefrody Member
Member

Re: DAX DATEDIFF Working Days and Others

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

Capture 1.PNG

 

But I am getting this error:

 

Capture 2.PNG

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

markefrody Member
Member

Re: DAX DATEDIFF Working Days and Others

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

Community Support Team
Community Support Team

Re: DAX DATEDIFF Working Days and Others

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.