Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Business Day Table

I have a calendar table that ranks business days pretty good as follows:

 

Rank = 
RANKX(
    FILTER(
        'Calendar',
        Calendar[If work day] = 1
            && Calendar[Month] = EARLIER( Calendar[Month] )
    ),
    Calendar[Date],,ASC
) 

 

My issue comes when there is a weekend as the last day in a month. For example, April 29th and April 30th in 2017 were on the weekend. So the formula above tags 4/29 and 4/30 as business day 21 when there were only 20 business days in April 2017. How can I adjust this formula to have 4/29 and 4/30 be tagged as business day 20?

 

 

 

The only hesitation I have then is the reverse side. Again for April 2017, April 1st was a Saturday. So I would want both 4/1 and 4/2 to show as business day 1 which is actually 4/3 in April 2017.

 

 

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    The picture below is an example

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      Is this possible?
      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage

        Hi Anonymous,

         

        Sorry for the delay.

         

        It should be feasible. Could you please share us your pbix file with One Drive or Google Drive if possible? So that I can make some proper tests.

         

        Thanks,
        Xi Jin.