Hi @Keith,
Thanks for that thought but there shouldn’t be a need for that. I make sure the code in the initial post of this thread AND ‘my shared version’ in the Analyst Hub are both up to date. Everyone can share their personal version of the Extended Date table as well, of course but I only maintain these two.
This is great Melissa. Just a question will the date table work if my fact table is in months. Would the relationship still be between Date table [date] to Fact Table [Month Ending]?
Yes, that would allow you, for example to allocate values to a higher granularity like weeks or days. There’s content available on that subject just search for allocation and/or forecasting if you want to learn more about that. Also look for handling mismatched granularities in Power BI, you can find that in the portal as well.
I hope this is helpful
Thanks, found the solution in one of the videos 
Hi Melissa, I am using your Date Function M Code from AH and still receiving the same errors on the Quarter & Year and Month & Year columns
@Schreg
Have you switched to ‘my’ AH shared version?
If so, create a new topic and share a sample file with this issue. Thanks.
A couple of weeks ago, @Brian created a LinkedIn post presenting and extolling the virtues of @Melissa’s wonderful M-code Extended Date Table.
LinkedIn post:
There was a comment on this LinkedIn post asking if there was a similar SQL Dates table, and, having seen many similar (although not nearly as extensive) examples in my (pre Power BI career) consulting work, I decided to create a quick brain-dump of the sort of SQL that I’ve seen used in government to create a [Dates] table.
My quick example uses a series of 10 SQL scripts (1 schema, 4 tables, 4 stored procedures, and 1 view) to create and access the data. The code is pretty-specific for a Canadian government environment (where I’ve had most of my experience with SQL [Dates] tables), where all labels must be available in both English and French and the terms used must come from an internal translation department (rather than, say, be generated programmatically).
I’ve added a few fields that are useful for Power BI (e.g., offsets) but didn’t add others (e.g., weeks). This [Dates] code uses an April 1 fiscal year start and a standard fiscal calendar (again characteristics of a typical Canadian government environment). This [Dates] code also uses Canadian federal holidays, and such, for those that are interested in its’ use, the “usp_PopulateHolidays” script should be adjusted to suit your individual requirements.
To implement, run the scripts in your SQL Server database environment (e.g., SQL Server Management Services, or SSMS) against any single database in order:
Each script is to be executed once, and all interfaces should be to the view, such that “is current…” and “is after today” flags are recalculated on each refresh. The “usp_PopulateHolidays” script should be adjusted when additional holiday are to be added and then the “usp_PopulateHolidays” and “usp_PopulateDates” scripts should be re-executed.
NOTE: This code is provided as-is for information purposes only, and has not been extensively tested. The use of these scripts is solely at the discretion of the end user; no responsibility is assumed by the author.
Hopefully this code can serve as a starting point for further development.
Greg
01. Create_Schema.Reference.sql (123 Bytes)
02. Create_Table.Reference.Months.sql (1.8 KB)
03. Create_Table.Reference.Days.sql (1.6 KB)
04. Create_Table.Reference.Holidays.sql (1.5 KB)
05. Create_Table.Reference.Dates.sql (2.7 KB)
06. Create_Procedure.Reference.usp_PopulateMonths.sql (2.3 KB)
07. Create_Procedure.Reference.usp_PopulateDays.sql (1.5 KB)
08. Create_Procedure.Reference.usp_PopulateHolidays.sql (7.8 KB)
09. Create_Procedure.Reference.usp_PopulateDates.sql (6.6 KB)
10. Create_View.Reference.vw_Dates.sql (2.1 KB)
@Melissa
I may be missing something, but I want to be able to set the date table to the beginning of the year for the minimum date in a specific table, and the end of the year (or financial year?) in that same specific table, is there a chance you could post the code for that to happen with generic names for the specific table?
I have tried to follow your instructions but failed.
I may also be missing something, but putting manual dates always needs updating … unles syou do 31/12/2050 and that causes issues with dates that are irrelevant.
Am I going about this wrong, or would a max and min dates (or end of year of the max) from a specific tables (say sales) be a better solution?
Happy to learn more.
John
Hi John,
I’ll let someone else answer the part about setting the date table to the beginning of the year for the minimum date in a specific date, although my next point sort of answers it (potentially).
When you use this awesome date table, and you are in the function to set the dates, I find the minimum date I need for the starting date as this “usually” does not change so there is no need to make it dynamic if it doesn’t need to be. So I will enter the start date of my date table as 01/01/2022 and the end date as 31/12/2022 (end date is a placeholder just now).
With regards to the end date of your date table, this needs to be dynamic.
I will create a blank query and place the below M Code into it
let
Source = Date.EndOfYear(List.Max(#"Your Table Name"[Date]))
in
Source
I’ve called this query DateEnd. In my case it will find the last date in my table I want the date table to be connected to…and through using Date.EndOfYear combined with List.Max it will dynamically update to the final day of the year as your data date range grows, making my date table dynamic. So as soon as you have a value that ticks over to 2023, then the date table will expand all the way until the end of 2023.
List.Max finds the last value, in this case, the last/oldest date in the date column you wish to use as the reference for your date table.
Date.EndOfYear simply finds the last day of the calendar year from the List.Max value.
I then go to my actual date table, into the Advanced Editor, and update my date table by entering DateEnd instead of an actual hard coded date. This will make your date table dynamic. as per below
let
Source = fxDate(#date(2020, 1, 1), DateEnd, null, null, null),
Usually, I’d hardcode the start date of the date table as you don’t want to run a dynamic query on the start date if you don’t need to.
DJ
Hi @jgriffit,
I trust the reply provided by @DavieJoe was helpful. If you require further assistance please create a new thread. In the mean time these resources might be beneficial.
Wow, so detailed and clear.
Many thanks David.
John
Many thanks for the follow up Melissa. So helpful.
Cheers
John
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
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.
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.
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.
@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.
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!!
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?