cancel
Showing results 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

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.

Thanks

Alice

1 ACCEPTED SOLUTION
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.

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.

5 REPLIES 5
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.

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.

Helper I

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)

Frequent Visitor

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

Super User

Are you sure you want to do that?

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