Forum Discussion

Benjamin_500's avatar
Benjamin_500
Advocate I
9 years ago
Solved

DATEDIFF Working Days

Hey all,   I am struggling on how to approach a problem,   I want to count the days between 2 dates, but only count working days.   My current formula is: DATEDIFF('Report'[DeliveryDate],NOW(),...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Benjamin_500,

    First you should create a holiday table including all holiday form Delivery start date to Today. Then create relationship between holiday table and your 'Repot' table. 

    I try to reproduce your scenario using date from 2017/1/1 to 2017/3/31.

    Search the holiday days for 2017, I use "List of federal Public Holidays of USA in 2017", and type it in Power BI table as followings.



    Create relationship between Holiday table and Date Table.



    Use WEEKDAY function to get weekdays, use RELATED function to get holidays. Use if function to decide if the day is work day.

     

    WeekDay = WEEKDAY(DateTable[Date])

Holiday = RELATED(Holiday[Holiday])

    If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))

     



    The 1 stands for the day is work day.

    Finally, create a measure to calculate the number od holidays. And create a card visual to display the result.

    Total work days = CALCULATE(COUNT(DateTable[Date]),FILTER(DateTable,DateTable[If work day]=1))



    If you have other issues, please let me know.

    Best Regards,
    Angelia

prakashacharya's avatar
prakashacharya
Helper I
9 years ago

How can i filter to show the workdays just for that month?

Archie1's avatar
Archie1
Frequent Visitor
8 years ago

Hi prakashacharya  and hmedinaa

 

Please find my soultion below for finding no. of working days excluding holidays & weekends.

 

WorkingDays_FiscalMTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[fiscalYear],'Calendar'[fiscalQuarter],'Calendar'[fiscalMonth]))

 

OR 

Use

WorkingDays_MTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[Year],'Calendar'[Quarter],'Calendar'[Month]))

 

Note: Assuming that you already had a calendar table with Fiscal year, Fiscal Quarter & Fiscal Month.

 Hope this helps!! 

Cheers,

Archie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Good morning,

     

    I've followed these instructions for calculate the working days. In fact, I have a table with the saturdays, sundays and holidays. But I'm not getting what I want. Could you help me?

     

    Also, I have a table like this:

    IDStart DateFinal DateDATEDIFFDATEDIFF1
    12301/01/201802/01/20181?
    12405/01/201810/01/20185?
    12515/01/201801/02/201817?
    12618/01/201820/01/20182?

     

    I got a DATEDIFF between dates but I want the DATEDIFF discounts the saturdays, sundays and holidays. I thoght about calculate how many saturdays, sundays and holidays are between start and final date and then rest it in DATEDIFF column. But I don't know how I should say it to PowerBI.

     

    Regards