Forum Discussion

Lexi's avatar
Lexi
Frequent Visitor
6 years ago

Time measures excluding weekends

Hello Power BI experts!   I am looking for help on some time calculations. I am fairly new to Power bi so would need step to step guide (teaching a baby to walk👶) Date table below:   Scenario 1:...
Lexi's avatar
Lexi
Frequent Visitor
6 years ago

Hi Ibendlin,

 

Thanks for replying. 

 

Weekends would include Saturday and Sunday. No different time zones only GMT. Business hours would be in fact 8 am till 4pm Mon-Fri. 

 

I do have a calendar I created using the formula Calenderauto(). Hope this is sufficient.

 

Thanks!

Lexi.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

One more thing . you have not specified how to handle exceptions, ie when one or both of your timestamps happen to be on weekends or on workdays but outside the agreed business hours? Do you penalize users for that?

  • Lexi's avatar
    Lexi
    Frequent Visitor
    6 years ago

    Hi again,

     

    I have tried all links with no success.

     

    The dax code, seems to be the closest thing but an error comes up: the start or end date in calendar function can not be blank. Even if I put the created date and completed date.

     

    Working Hours =
    VAR filtered =
    FILTER (
    ADDCOLUMNS (
    CROSSJOIN (
    CALENDAR ( [Created Date],[Completed Date] ),
     
    With the exceptions, I was thought that the "timer" would count from the next date or time ie Mon-FRi 8am-5pm. 
     
    Thanks,
    Lexi
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Lexi ,

       

      Can you please share a sample pbix file.

       

      Regards,

      Harsh Nathani