Forum Discussion
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.
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.
3 Replies
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
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.
