Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Networkdays issue with filtering holidays

I have an issue: Approach of calculation network days does not work. I Prepared everything by instruction which is presented here (https://www.youtube.com/watch?v=9M1V_m-oEzc)

Link on pbix.

https://app.box.com/s/8jqxm88ik0850lh2llt6zrfwyyek056r

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi Anonymous,

     

    We can take the following steps to meet your requirement.

     

    1. Update one of your formulas 

    From

    WorkingDays = IF(OR('Date'[Date]=6, 'Date'[Date]=7),0, 1)

    To

    WorkingDays = IF(OR('Date'[WeekDay]=6,'Date'[WeekDay]=7),0,1)


    2. Create a new column.

    workday = IF('Date'[WorkingDays]=1,DAY('Date'[Date]),BLANK())


    3. Then we can get the result we need.

     

     

    For more details, please check the pbix as attached.

    https://www.dropbox.com/s/3b68yfqbdz3vo9n/NetWorkDays2.1.pbix?dl=0

     

    Regards,
    Frank

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    Hi Anonymous,

     

    Based on my test, I can calculate the net workdays by the way as the video.


    1. Create a Date table and a holiday table.
    2. Add two columns in the Date one.

    weekday = WEEKDAY('date'[Date])
    weekdays = IF(OR('date'[weekday]=6,'date'[weekday]=7),0,IF(ISBLANK(RELATED(Hiliday[holi])),1,0))

     


    3. To get the new workdays, we can use this measure as below.

    days = CALCULATE(SUM('date'[weekdays]),FILTER('date','date'[weekdays]=1))

    Then we can get the result as below.

     


    For more details, please check the pbix as attached.

     

    https://www.dropbox.com/s/iyrynlunt15xrm3/Networkdays%20issue%20with%20filtering%20holidays.pbix?dl=0

     

    By the way, I cannot get your pbix you shared. Could you please share that to me again if necessary?

     

    Regards,
    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Frank!

       

      Could you please check this link: https://app.box.com/s/8jqxm88ik0850lh2llt6zrfwyyek056r

      I see that it works in case when you have additional data by holiday.

      But I need to exclude just weekends (Saturday and Sunday) 

      As a result I would like to see something like that:

       

      Untitled.png

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        8 years ago

        Hi Anonymous,

         

        We can take the following steps to meet your requirement.

         

        1. Update one of your formulas 

        From

        WorkingDays = IF(OR('Date'[Date]=6, 'Date'[Date]=7),0, 1)

        To

        WorkingDays = IF(OR('Date'[WeekDay]=6,'Date'[WeekDay]=7),0,1)


        2. Create a new column.

        workday = IF('Date'[WorkingDays]=1,DAY('Date'[Date]),BLANK())


        3. Then we can get the result we need.

         

         

        For more details, please check the pbix as attached.

        https://www.dropbox.com/s/3b68yfqbdz3vo9n/NetWorkDays2.1.pbix?dl=0

         

        Regards,
        Frank