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
Solution 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)