cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cking2019
Frequent Visitor

Custom Date Table with Cumulative working days

Good Morning, 

 

I was hoping for some help with Power Query on creating a custom date table. I have searched for this solution for many weeks and have yet to be able to find it. I am currently utilizing an excel file that lists the max workdays for each month in each row. I would like to move away from having to connect to an excel work book each time i want to have a date table but also want the ability to utlize in multiple reports without having to recreate calculated columns. Any help would be appreciated. Screenshots below for the expected outcome. 

cking2019_0-1598360024794.png

 

1 ACCEPTED SOLUTION

I have actually figured this out (At least for now). I added two group by steps. One to group by Max value of the Month to date work days and one for the yearly work days. I utilized the all rows feature and kept in the source table. See below for the result. 

 

cking2019_0-1598367840763.png

 

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User IV
Super User IV

@cking2019 - Well, if it is anything like the DAX way of doing it, https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109 you would need a way to generate a calendar table and determine which days are not weekends.

 

Maybe @ImkeF and/or @edhans can help, I couldn't find anything obvious in the Power Query function reference:

https://docs.microsoft.com/en-us/powerquery-m/date-functions


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

@ me in replies or I'll lose your thread!!!

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!




@Greg_Deckler  Thank you! I have my calendar table built with cumulative count of work days in the month. I just want the static number to run a workdays remaining analysis and some other measures based on the amount of workdays. I know i can add a calculated column but that would require me to re make the column each time i use the date table in a report. I have seen a solution from @ImkeF however, each time I attempt to utlize it in the table it tells me there is circular refrence and i believe this is because my holidays are joined in my date table. I am trying to get a standard date table for our company while not having to connect to another data source (Excel File). 

 

It is a bit complicated in Power Query. 

I have actually figured this out (At least for now). I added two group by steps. One to group by Max value of the Month to date work days and one for the yearly work days. I utilized the all rows feature and kept in the source table. See below for the result. 

 

cking2019_0-1598367840763.png

 

View solution in original post

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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors