Power BI to show workday for each day in the month

[lawsc76]

hi

I would like to build "Workday in the month"in the date table. Need to seek your advise the dax formula.

Example table below. 

I would like to show workday in the month exclude weekend. Meaning 1st weekday of March is 1,2,3 ... , by last weekday of March the number show in the column should be 24.

In April, the workday number of 1st weekday should start from 1 again instead of continuous number workday 25.

Appreciate your support to advise DAX formula

Thanks

Alice

[v-rzhou-msft · Accepted solution]

Hi @lawsc76 

You can try my measure:

Workday = 
IF (
    'Date'[Weekday] IN { 6, 7 },
    BLANK (),
    RANKX (
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[Month] = EARLIER ( 'Date'[Month] )
                && NOT ( 'Date'[Weekday] IN { 6, 7 } )
        ),
        DAY ( 'Date'[Date] ),
        ,
        ASC
    )
)

If you want to show workday in format workday N, try this measure.

WorkdayN = 
VAR _Workday =
    IF (
        'Date'[Weekday] IN { 6, 7 },
        BLANK (),
        RANKX (
            FILTER (
                'Date',
                'Date'[Year] = EARLIER ( 'Date'[Year] )
                    && 'Date'[Month] = EARLIER ( 'Date'[Month] )
                    && NOT ( 'Date'[Weekday] IN { 6, 7 } )
            ),
            DAY ( 'Date'[Date] ),
            ,
            ASC
        )
    )
RETURN
    IF ( _Workday <> BLANK (), "Workday" & " " & _Workday )

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[CracktheCode85]

Hi v-rzhou-msft!
I am facing a similar issue but when I try to use your measure (the first one) I get an error around the EARLIER function. 
Is there another workaround for this that you know of? 

[Anonymous]

@lawsc76 considering Sunday as weeknum 1, you can create following column:

Weekday in the month = SWITCH(WEEKDAY(Datedim[Date]),1,0,2,2,3,3,4,4,5,5,6,6,7,0)

[lawsc76]

thank you for advise.

What i need to show in the workday column is each number workday for each date in the month and start the workday number in the following month.

If you refer to table below, 

1/3 - workday1

2/3 - workday2

3/3 - workday3

4/3-workday4

5/3-workday 5

8/3 - workday 6

9/3-workday 7

1/4 - workday1

2/4 - workday2

5/4-workday 3

8/4 - workday 4

9/4-workday 5

Please advise.

[lbendlin]

Are you sure you want to do that?

It's the weekend! Or is it? - Microsoft Power BI Community