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
Anonymous's avatar
Anonymous
Not applicable
8 years ago
Is this possible?
v-xjiin-msft's avatar
v-xjiin-msft
Solution 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
      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.