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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

v-cherch-msft Super Contributor
Super Contributor

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

v-cherch-msft Super Contributor
Super Contributor

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 59 members 1,285 guests
Please welcome our newest community members: