Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lawsc76
Frequent Visitor

Power BI to show workday for each day in the month

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.

 

lawsc76_0-1614563182059.png

 

Appreciate your support to advise DAX formula

Thanks

Alice

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

3.png

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. 

 

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

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.

3.png

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. 

 

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
Not applicable

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

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

 

lawsc76_0-1614655337491.png

Please advise.

lbendlin
Super User
Super User

Are you sure you want to do that?

 

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.