Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Weekday number by month

My main goal for my Power BI report is to be able to compare amounts from the 1st weekday, 2nd weekday, 3rd weekday...etc, of a prior year to the 1st weekday, 2nd weekday, 3rd weekday of the current year. 

 

So I have a calendar table that has dates from 1/1/14 - 12/31/20. I then have a Weekday column as follows:

 

 

Weekday = WEEKDAY('Calendar'[Date])

Then I brought in another table that has holidays. I have another column in my calendar table that labels each holiday:

 

 

 

Holiday = RELATED('Holiday'[Holiday])

 

 

Then I have another column to determine if the day is a "business day" (so a weekday that is not a holiday):

 

 

If work day = IF(OR('Calendar'[WeekDay]=1,'Calendar'[WeekDay]=7),0,IF(ISBLANK('Calendar'[Holiday]),1,0))

 

Then I have a rank formula to rank all the "business days":

 

Rank = 
VAR
    Months = [Month]
Return

IF([If work day] = 0, BLANK(), RANKX(FILTER('Calendar','Calendar'[If work day]=1),'Calendar'[Date],,ASC))

This column references another column I created to label each month/year combo:

 

Month = MONTH([Date]) & " " & YEAR([Date])

Finally, I have my attempt at getting the weekday number for each month:

 

Weekday Number = 
IF([If work day] = 0, BLANK(),COUNTROWS (
    FILTER (
        'Calendar',
        [Rank] <= EARLIER ( [Rank] )
            && [Month] = EARLIER ( [Month] )
    )
))

This code kind of works, but it starts with the number 10 rather than the number 1 for some reason?

 

 

Any ideas on what I'm doing wrong? Or is there an entirely different way to do what I'm trying to accomplish that is easier?

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Am I missing an entriely easier way to do this?

    • sdjensen's avatar
      sdjensen
      Solution Sage
      8 years ago

      For this to work I think you will need to have a value in all of your rows, so if the row is a none working day it should return the same number as the previous (EARLIER) row. If your amounts returns values for none working days you should be able to not include these amount by setting a filter in your report that [If work day] = 1 or make it easier for you users to understand by creating a Working Day column that returns Yes/No.

       

      I basically think you just need a small change to you Rank column and then there will be no need for your "Weekday Number" - Calendar[Year Month] is similar to your month/year combo, I just put year first to ensure the correct sort order.

      RANKX(
    FILTER(
        Calendar,
        Calendar[If work day] = 1
            && Calendar[Year Month] = EARLIER( Calendar[Year Month] )
    ),
    Calendar[Date],,ASC
)
      • sdjensen's avatar
        sdjensen
        Solution Sage
        8 years ago

        Anonymous - I am currious if you solve your problem?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale