cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## Cumulative Weekday

Dear All,

Could you please share what is the Dax code for the cumulative working day for the following condition below:-

1. First working day of the month assigned as 1, then cumulative adding the weekday (without weekend is considered) during the month (i.e Dec'17 having 21 working days, with 1 to 3 Dec'17= 1, 4 Dec'17 =2, etc., total working days for Dec'17 = 21)

2 ACCEPTED SOLUTIONS
Super User

Hi @tanct,

Try this calculated column formula

`=CALCULATE(COUNTROWS('DIM-Calendar'),FILTER('DIM-Calendar','DIM-Calendar'[Date]<=EARLIER('DIM-Calendar'[Date])&&'DIM-Calendar'[Date]>=EARLIER([Date])-DAY(EARLIER([Date]))+1&&'DIM-Calendar'[Weekend]=1))`

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Employee

@tanct

You can also try to create a calculated column with following formula.

```Cumulative Weekday =
CALCULATE (
SUM ( 'DIM-Calendar'[Weekend] ),
FILTER (
'DIM-Calendar',
'DIM-Calendar'[Date] <= EARLIER ( 'DIM-Calendar'[Date] )
&& 'DIM-Calendar'[Year] = EARLIER ( 'DIM-Calendar'[Year] )
&& 'DIM-Calendar'[Month] = EARLIER ( 'DIM-Calendar'[Month] )
)
)
```

Best Regards,
Herbert

6 REPLIES 6
Employee

@tanct

You can also try to create a calculated column with following formula.

```Cumulative Weekday =
CALCULATE (
SUM ( 'DIM-Calendar'[Weekend] ),
FILTER (
'DIM-Calendar',
'DIM-Calendar'[Date] <= EARLIER ( 'DIM-Calendar'[Date] )
&& 'DIM-Calendar'[Year] = EARLIER ( 'DIM-Calendar'[Year] )
&& 'DIM-Calendar'[Month] = EARLIER ( 'DIM-Calendar'[Month] )
)
)
```

Best Regards,
Herbert

Community Champion

Hi @tanct

Add this calculated column for Cumulative Working Days

```=
CALCULATE (
SUM ( 'Dim-Calendar'[Weekend] ),
FILTER (
'Dim-Calendar',
'Dim-Calendar'[Day] <= EARLIER ( 'Dim-Calendar'[Day] )
&& 'Dim-Calendar'[Month] = EARLIER ( 'Dim-Calendar'[Month] )
)
)```

Regards
Zubair

Regular Visitor

Dear Zubair,

It seems not working.

03.07.2016 should be = 1 and 04.07.2016 = 3 (instead of 2 and 4), the weekend seem unable to adding up correctly.

.

Community Champion

Hi @tanct

Shouldn't 04.07.2016 be = 2 (second working day)

Could you share file via onedrive or google drive?

Give this code a try for the time being

```=
CALCULATE (
SUM ( 'Dim-Calendar'[Weekend] ),
FILTER (
'Dim-Calendar',
'Dim-Calendar'[Day] <= EARLIER ( 'Dim-Calendar'[Day] )
&& 'Dim-Calendar'[Month] = EARLIER ( 'Dim-Calendar'[Month] )
&& 'Dim-Calendar'[Month] = EARLIER ( 'Dim-Calendar'[Month] )
)
)```

Regards
Zubair

Regular Visitor

Hi,

You may go to table DIM Calendar.

Basically, I have identified the weekend for each of the month (in column Weekend), just pending to sum up the computed weekend figure to arrive working day of the month. Thanks in advance.

Super User

Hi @tanct,

Try this calculated column formula

`=CALCULATE(COUNTROWS('DIM-Calendar'),FILTER('DIM-Calendar','DIM-Calendar'[Date]<=EARLIER('DIM-Calendar'[Date])&&'DIM-Calendar'[Date]>=EARLIER([Date])-DAY(EARLIER([Date]))+1&&'DIM-Calendar'[Weekend]=1))`

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors