cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mohsen_2020
Helper III
Helper III

Create a measure calculating Working Days

Dear Team, 

@Ashish_Mathur 

I need your support to creeate a measure calculating number of working days knowing that Saturday & Friday days are off, i mean to exculude both "Friday & Satureday" from working days calculations. 

 

Notes:

1-Rep name field name is: territory.

2-"Call Time" highlighted field is the right date and time.

File link:  https://drive.google.com/file/d/1ATq6hyGl8urx3_wAMjnGesh3677zAidO/view?usp=sharing

 

Regards,

Mohsen

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Mohsen_2020
Helper III
Helper III

Hi , I need it in a measure so i can put it in a card will display the working days as a number. 

@Ashish_Mathur  , Please help. 

Regards,

Mohsen

Hi @Mohsen_2020 ,

1. There are seven rows without any data in your data table.

2. In Power Query split column Call Time in date part and time part.

3. Now you can calculate your workin days like this:

 

14-05-_2021_23-57-07.png

 

Sum of weekdays = 
CALCULATE (
    DISTINCTCOUNT ( report1617266304758[Date] ),
    FILTER (
        report1617266304758,
        WEEKDAY ( report1617266304758[Date] ) IN { 1, 2, 3, 4, 5 }
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

 

FrankAT
Super User II
Super User II

Hi @Mohsen_2020 ,

open your data in Power Query and add a new column that holds the information 'Is Workingday'. 1 is workingday, 0 isn't workingday. Use the following M-code (4 = Friday, 5 = Saturday):

 

= Table.AddColumn(#"Changed Type", "Is Workingday", each if Date.DayOfWeek([Call Time]) = 4 or Date.DayOfWeek([Call Time]) = 5 then 0 else 1, Int64.Type)

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors