No worries @jgriffit hopefully it answered your question.
If this still hasn’t solved it, then please start a new thread and we can complete there.
David
1 Like
First time poster.
This may be the most important component in any report I will build in the future! I have turned this M Code on to all colleagues and we have made it apart of our standard template! Thank you so much!
I would like to see this code extended to incorporate the Agile Sprint scheduling (I have added them as calculated columns but would like to see it embedded within M code to be cleaner:
Sprint Number
Sprint Start Date
Sprint End Date
Parameters to add when invoking include:
Sprint Duration (Days)
Sprint Start Day of Week
Sprint Number start (what sprint number do you wish to begin with?)
This will go a LONG way in supporting operational or transactional results/ROI observed as AGILE scrum teams release new or enhanced functionality for business organizations.
1 Like
Hi @ClarkeM,
Welcome to the forum!
I’m glad this date table has been helpful and is used in your org.
As for your question, I would be happy to look into that for you but can you please create a new topic (separate) topic. That will ensure its easier to find for others in future as well.
You can tag me by prefixing my name with an @
Thanks!
Hi Melissa,
Thanks for the great code! I’m just starting out with using power query and M and wanted to know if there a way that I can customize the Fiscal months to start on the first Monday of the month? I read the notes in the code and it says that if you select January for the first fiscal that it will start the new year on the first monday, but that did not happen when I invoked. Any help would be great.
1 Like
First welcome to the Forum!
.
That is true for Weeks, ISO Years and ISO Quarters, in regard to Months it only contains Calendar Months.
.
Q: Does your fiscal year start in January?
Please create a new topic if you need assistance customizing this M code. Provide a clear description of your requirement and pay special attention to describing how dates should be handled at period start and/or period end.
2 Likes
@Melissa thanks! I’m so happy that I found it… so much information/knowledge to take in! I’ve created a new topic on what I am trying to accomplish.
2 Likes
Thanks for the great work, Melissa! I use this all the time.
I do have a question, though. How can this be used in a dataflow? As it references a function… I can’t seem to figure out how to make it work.
Thak you again!!
1 Like
Is it possible to add another column like the Relative Networkdays column that sums the number of workdays by each month versus cumulative for the year?
I believe I just did this yesterday, so curious to know the responses from the community as well!
I used Bas’s video as a guide, and then used the Add Column > Invoke Function option, against the Start of Month and Current Day. It seems to have worked as expected. I also included a list of holidays. Bas’ Video
Hi I am trying to specified the duration between the days on a monthly basis (like 1/1/2016, 2/1/2016).Jan, feb. Please how do i go about that
Thanks very much for this very useful table! I am new to Power BI, and am trying to figure out how I would tackle adding Week to Date and Previous Week to Date into the Dates table mix, as our business is very seasonal and comparing this week to the same last year is one of the things I need to do, but while I can just look at the week numbers, sales build through the week, so if it’s Wednesday of Week 10, I want to see Monday - Wednesday Week 10 sales vs the same Monday-Wednesday Week 10 last year.
Any suggestions or code to add in to achieve this would be very appreciated!
Thanks,
Melissa
Hello @mphelvin,
Please create a new topic if you need assistance customizing this M code. Provide a clear description of your requirement and pay special attention to describing how dates should be handled at period start and/or period end.
To help the forum members further analyze your current state and visualize your issue, could you please provide as many as you can of:
• Your work-in-progress PBIX file uploaded rather than linked to using sanitized data if necessary
here’s @BrianJ’s YouTube video on how to anonymize data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• Your dataset as an uploaded rather than linked to Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.
Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.
@Melissa thanks for a great table. However I am missing a useful feature I have seen in other DateTables which comes handy from time to time and that is YTD flag (true/false).
Is there any reason you did not include it or is it included somewhere but I am not seeing it?
I noticed the column PYTD, but as mentioned not YTD.
Thanks.
I don’t typically report at day-level. I need to now, but i see that “Day of Year” column shown below:
is not included when the function is invoked.
Using Version = " 2.01: full code review"
Hi @HufferD,
On line 137, remove this: “Day of Year”,
from the list as illustrated below.
That modification will include that column in the output.
I hope this is helpful
1 Like
Hi @Melissa your date table is brilliant! I’ve modified it a little for my own purposes as I need to know whether a date is a public holiday in NZ and also whether a date is a public holiday in Australia. I have different columns for each country and it is working just as I want it to.
My issue is though, that I want to use the code inside a Dataflow. An issue arises as the Dataflow greys out the ability for me to select the table and column where my NZ and Australian holiday dates are. Do you know if there is a way to do this within a Dataflow?
I’ve attached a screenshot of what I’m seeing in the Power BI Service Dataflow. You can see the holiday fields are greyed out.
Hi @5thGreen,
You can work around that, follow these steps.
Invoke the date table M function without specifying holidays (these parameter need to be optional).
Are holidays in a separate “table” query?
Right-click the NZ holiday date column header and select Add as new query. Give the new query a proper name and Disable its load. Go back to the Date table query and update the holiday parameter by referencing the NZdateList query by name. Repeat for Australia.
Thanks @Melissa I will try that when I’m back in front of my computer in the morning. Just a couple of points to clarify if I may?
- Yes, the holiday parameters are optional
- The separate table queries you mention, my holiday tables are code that I drop into a blank query. The code specifies the dates that are holidays and after clicking OK in the editor it produces the table. Which i then normally select within the date table query when I’m in PBI Desktop. So yes, they’re in separate tables. I assume that’s what you’re meaning?
- I presume i create those tables within a new blank query in the dataflow and then give each table a name as you mention above
- When you say “update the holiday parameter by referencing the NZDateList query by name”, are you meaning i should change the actual M code to reference the table by name before invoking it rather than having it be written as it currently is whereby it’s an optional list? That might be beyond my M code knowledge, but I’ll certainly give it a try in the morning and be back if I’m having trouble.
Thanks again, i really appreciate it! 
@5thGreen, Paul,
For simplicity I will assume your date table function query is called: fxDate, you can invoke it without specifying optional arguments - to generate a Date table.- Perfect, if you need these holiday tables, as a table somewhere else OR be available to connect to, leave them as is.
- For this next step there is no need to code, right-click the ‘holiday date’ column header in your holiday table (from 2) and select ‘Add as new query’. This will add a new query that returns a list of dates. Give this list a proper name - lets call it: holidayListNZ and disable load for this query. Repeat these actions for Australia as well.
- When you invoked the date table M function, fxDate, it returned code like this:
fxDate( #date(2020, 1, 1), #date(2025, 12, 31), null, null, null, null)
the 4th and 5th argument correspond to NZHolidays and AustHolidays, respectively. To update the query we can replace the 4th argument, a null, with holidayListNZ (from 3) and the 5th with holidayListAust, all done.
1 Like
Hi @Melissa
Thank you so much! This has worked a treat and given me exactly as I wanted it to. I’m really pleased!
I actually replaced the wrong arguments in my first attempt so deleted the invoked date table to do it again. I noticed that when I did this, the query had changed and a drop down option had appeared for NZ Holidays and Aust Holidays which allowed me to select a value or a query. After selecting the query option I could then choose either holidaylistNZ or holidaylistAust and then invoke the function. Everything ran and generated exactly what I want. See the screen shots below.
Thanks again, I’m very grateful
Paul
1 Like