cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculating the net working days using M

Hi Everyone,

I see a lot of posts on here about how to calculate the net working days using DAX. However I don't see anyone showing the same solution using M. Reason I want to add this as a column in PowerQuery is so that I can group the results. That's why I need to have a column that I can apply conditional logic to.

Scenario:

I have a date dimension table called "Date" with a column called "Working Days excl. Holidays" that returns a true or false value on whether it is a working day or not. TRUE = Working Day, FALSE = Not a working day.

I have another table, called "NCReport" that contains the column "NCR_Date" and "Today's Date". Essentially, what I want to do is to add a new column in this table that will calculate the number of working days between "NCR_Date" and "Today's Date", using "Working Days excl. Holidays" = TRUE.

2 REPLIES 2
Highlighted
Super User III

## Re: Calculating the net working days using M

Hi,

this could probably be solved in much more elegant way, but this is one way of getting the number of workdays

In the NCReport table, add a custom column with this code

`=List.Numbers([NCR_date],[Today]-[NCR]+1)`

and expand this to new rows. This will give you one row for each day between NCR_date and today. From the Home tab, use Merge queries, and merge NCReport with your dimDate on the column you just created. Expand the resulting column, and choose only your WorkingDay column. Then on the transform tab, use Group By, and group NCReport by NCR_date and Today, and for the new column do a sum over the WorkingDay column from the merge. If working day is true/false, you might have to convert it to 1/0 to get the sum to work

Highlighted
Frequent Visitor

## Re: Calculating the net working days using M

Hey Sturlaws, thanks for the suggestion. I see where you're going with this, but I don't think that's going to work as I have tens of thousands of rows already in my NCReport table, in addition, if I'm expanding each row by the number of days between NCR_Date to Today's date. it's going to get to an unmanageable size...

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors