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(...
Need Help
v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

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.

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi Anonymous,

     

    Check this, hope it works for you:

     

    Business Day = VAR RankID = RANKX(
    FILTER(
        'Calendar',
        Calendar[If work day] = 1
            && Calendar[Month] = EARLIER( Calendar[Month] )
    ),
    Calendar[Date],,ASC
)
Return
IF('Calendar'[If work day]=1, RankID, RankID-1)

     

    Thanks,
    Xi Jin.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-xjiin-msft Sorry for the delay and thank you for the response. That works, however, I also need 4/2/2017 which you have circled in your screenshot to show up as 1. So if a month starts on a weekend, then those days should be recognized as business day 1. However, if a month ends on a weekend, those days should recognized as the last valid business day or the max business day for that particular month. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        v-xjiin-msft Nevermind, I think I answered my own question. Just added one more condition to your example:

         

        Business Day2 = 
VAR RankID =
RANKX(
    FILTER(
        'Calendar',
        Calendar[If work day] = 1
            && Calendar[Month] = EARLIER( Calendar[Month] )
    ),
    Calendar[Date],,ASC
) 
RETURN
VAR RankID2 =
IF('Calendar'[If work day] = 1, RankID, RankID - 1)
RETURN
IF(RankID2 = 0 ,1, RankID2)