cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Calculate no. of working days that have passed in the year so far

Hi, 

 

I have a calendar table where I'm using a calculated column to calculate the no. of working days in each month this year. 

 

Based on this column and the current date, I want to calculate the no. of working days that have passed year to date. Please see screenshot below for no. of working days by month. Essentially, I want this formula to return 21 for January, 20 for Feb and 19 for March (since based on today's date we have 2 more working days left in March) and the total to be 60. I have tried a few different formulas but can't seem to get the desired result, mainly the total is almost always incorrect. 

 

Thank you for any suggestions!

 

Working Days Year.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Calculate no. of working days that have passed in the year so far

YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"))

 

But for that, you need have a filter of the date on the page

or try like

YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Calculate no. of working days that have passed in the year so far

YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"))

 

But for that, you need have a filter of the date on the page

or try like

YTD Sales = CALCULATE(SUM('Date'[Working Day]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User IX
Super User IX

Re: Calculate no. of working days that have passed in the year so far

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

If you have a Calendar, then you should be able to create a column like:

Working Day = IF(WEEKDAY([Date],2)<6,1,0)

 

You should then just be able to SUM the Working Day column to get the number of working days and the totals should be correct.

 

If you are using some kind of measure, you may have a measure totals issue. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Calculate no. of working days that have passed in the year so far

Hi @kartiklal18

Take year 2020 for example:

1.Create a calendar table using below dax expression:

 

 table = CALENDAR(DATE(2020,1,1),DATE(2020,12,31)) 

 

2.Then create 3 calculated columns:

 Month = FORMAT('table'[Date],"MMMM") 

 

weekday = WEEKDAY('table'[Date],2)

 

Working day = IF('table'[weekday]<>6&&'table'[weekday]<>7,1,0)

 

3.Fianlly create a measure as below:

Measure = IF(MONTH(MAX('table'[Date]))=MONTH(TODAY()),CALCULATE(SUM('table'[Working day]),'table'[Date]<=TODAY()),SUM('table'[Working day]))

 

And you will see:

Annotation 2020-03-31 112312.png

 

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

Highlighted
Helper II
Helper II

Re: Calculate no. of working days that have passed in the year so far

Hi all,

 

Thanks for all your suggestions. @amitchandak , your solution works and its what I needed. 

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors