@Keith ,
Totally kidding - I completely appreciated the extra set of eyes and your great catch.
- Brian
1 Like
@Keith Only ever meant as a compliment, it was genuinely a good spot. Besides, we need someone to keep an eye on @BrianJ 
I think someone else had this issue but I didn’t see how it was resolved. The v2 Extended Date Table from the Analyst hub returned errors in “Quarter & Year” and “Month & Year”:
Hi @Schreg,
I’m not experiencing the reported issue, are you using ‘my version’ from the AH or someone elses?
pretty sure I used v2 (Aug 2022) from @BrianJ
Hi @Schreg,
Thank you for the feedback. Sadly I can only maintain/update code I’ve submitted to the AH so if you want to be sure that you have ‘the most recent’ version of the Extended Date table, please make sure to grab mine, thanks.
just a question @Melissa. Is there a date on AH on when it was loaded with a revised date. If not maybe it should be included in the description or in the code.
just a thought
thanks
Keith
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.
3 Likes
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 
1 Like
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:
- 1-create schema
- 2-5-create tables
- 6-9-create stored procedures
- 10-create view
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)
5 Likes
@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
1 Like
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
2 Likes
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.
1 Like
Wow, so detailed and clear.
Many thanks David.
John
1 Like
Many thanks for the follow up Melissa. So helpful.
Cheers
John