skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Re: Net Work Duration

    Re: Net Work Duration

    10-01-2018 03:52 AM

    NVieira
    Regular Visitor
    8602 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Net Work Duration (Working Hours)

    ‎08-06-2018 07:46 AM

    Similar to Net Work Days, Net Work Duration calculates the total duration between two date/time columns taking into account non-working days (weekends) as well as the start and end times for a work day. This version calculates duration in minutes, although this can be easily changed.

     

     

    mNetWorkDuration = 
    // Get the start and end dates
    VAR __dateStart = MAX([Date_Start])
    VAR __dateEnd = MAX([Date_End])
    // Calculate the Net Work Days between the start and end dates
    VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
    // Set this to the start of the work day (7:30 AM)
    VAR __startTime = TIME(7,30,0)
    // Set this variable to the end of the work day (6:00 PM)
    VAR __endTime = TIME(18,0,0)
    // Calculate the duration of a full day, in this case in minutes
    VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
    // Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
    VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
    // Calculate the total duration of all full days.
    VAR __fullDaysDuration = __fullDays * __fullDayMinutes
    // Calculate the start time of the current record
    VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
    // Caclulate the duration of time for the first day
    VAR __startDayDuration = DATEDIFF(__startDayTime,__endTime,MINUTE)
    // Calculate the end time of the current record
    VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
    // Calculate the duration of time for the last day
    VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime,MINUTE)
    // The total duration is the duration of all full days plus the durations of time for the first and last days
    RETURN 
    IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)

     

     

     

     

     

     

    eyJrIjoiYmNhYzJjY2YtZTBiMC00MDc4LThjNjAtM2YzOGE2M2JkMTdiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Preview file
    24 KB
    NetWorkDuration.pbix
    Labels:
    • Labels:
    • Other
    • Time Intelligence
    • Totals
    Message 1 of 11
    8,811 Views
    1
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    spriyanshu
    spriyanshu
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-18-2023 03:55 AM
    Hi @Greg_Deckler 
    I am trying to use your code for same kind of calculation but no need to account for holidays. Will bottom modification willl work.
    My Use case: i just want to calculate time (in minutues) used in maintenance window between 1 am to 6 am.

    measure =
    VAR __dateStart = MAX([INCIDENT STARTED])
    VAR __dateEnd = MAX([SERVICE_RESTORED])
    VAR __startTime = TIME(1,0,0)
    VAR __endTime = TIME(6,0,0)
    VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
    VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
    VAR __startDayDuration = DATEDIFF(__startDayTime,__endTime,MINUTE)
    VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
    VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime,MINUTE)
    RETURN  
    IF(DATEDIFF(__dateStart,__dateEnd,MINUTE), __startDayDuration + __endDayDuration)
     
    Message 11 of 11
    664 Views
    0
    Reply
    Danilo_Castillo
    Danilo_Castillo
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-21-2022 09:22 AM

    @Greg_Deckler  Hi dear friend.

     

    I was reading the whole thread and found it very interesting. I'm just trying to do the calculation.

     

    It would be very useful if the time is outside working hours,  the duration time is taken from the next working day starting on working hours (excluding holidays, Saturday noon and Sundays).

     

    example: 

    Working days: monday to friday working hours 8 am -  5 pm  ,  saturday from 8 am - 1 pm

    exclude holidays and sundays

     

    • start date: friday 11 pm
    • end date: monday  9 am
    • duration: 1 hour

     

    I really appreciate your help

     

    Message 9 of 11
    1,584 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to Danilo_Castillo
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-21-2022 11:44 AM

    @Danilo_Castillo Hey Danilo!! You are in luck my friend, I wrote a Working Hours version of this! Net Work Duration (Working Hours) - Microsoft Power BI Community

    Might not be exactly what you need but probably between the two we can get you there! Let me know!


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 10 of 11
    1,574 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-18-2022 01:05 PM

    Hi Greg,

     

    This is just what I need, to ignore any non work hour time, and if possible calculate Saturday halftime as working day.

    Message 8 of 11
    2,175 Views
    0
    Reply
    Diego09
    Diego09
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-15-2022 11:18 AM

    do you want it to ignore any non work hour time?

    YES


    how do you average mNetWorkDuration?
    Message 7 of 11
    3,503 Views
    0
    Reply
    NVieira
    NVieira
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-14-2018 09:13 AM

    Hi,

    I was trying to use this measure, but if we have dates that are outside working hours, the values are not correct ( see attachment).

    Preview file
    59 KB
    Message 2 of 11
    8,655 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to NVieira
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-14-2018 10:45 AM

    If this works for you, I will update the Quick Measure:

     

    mNetWorkDuration = 
    // Get the start and end dates
    VAR __dateStart = MAX([Date_Start])
    VAR __dateEnd = MAX([Date_End])
    // Calculate the Net Work Days between the start and end dates
    VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
    // Set this to the start of the work day (7:30 AM)
    VAR __startTime = TIME(7,30,0)
    // Set this variable to the end of the work day (6:00 PM)
    VAR __endTime = TIME(18,0,0)
    // Calculate the duration of a full day, in this case in minutes
    VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
    // Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
    VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
    // Calculate the total duration of all full days.
    VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
    // Calculate the start time of the current record
    VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
    VAR __startDayTime1 = SWITCH(
                                TRUE(),
                                __startDayTime>__endTime,__endTime,
                                __startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
                                __startDayTime
                            )
    // Caclulate the duration of time for the first day
    VAR __startDayDuration = DATEDIFF(__startDayTime1,__endTime,MINUTE)
    // Calculate the end time of the current record
    VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
    VAR __endDayTime1 = SWITCH(
                                TRUE(),
                                __endDayTime>__endTime,__endTime,
                                __startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
                                __endDayTime
                            )
    // Calculate the duration of time for the last day
    VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime1,MINUTE)
    // The total duration is the duration of all full days plus the durations of time for the first and last days
    RETURN  
    IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)

    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 4 of 11
    8,651 Views
    0
    Reply
    eduardo_alda
    eduardo_alda
    Frequent Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-19-2018 12:49 PM

    Hey Greg many thanks for your inputs, could you also consider holidays , based on another "holidays" table?

    Thank you so much!

    Message 6 of 11
    8,442 Views
    0
    Reply
    NVieira
    NVieira
    Regular Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-01-2018 03:52 AM

    Hi Greg,

    Thank you very much for your help.
    I still get negative values when both start/end time are outside working hours.

    Date Start - 07-07-2018 21:27:00

    Date End - 08-08-2018 00:05:00 

    I get -445


    In this cases i was expecting to have 0 instead of negative values.

     

    Beside that, how can i get an average of the mNetWorkDuration?

    Message 5 of 11
    8,602 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to NVieira
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-14-2018 10:28 AM

    So, if the time is outside of the work hours, how would you want the work hours to be calculated, do you want it to ignore any non work hour time?


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 3 of 11
    8,652 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices