Extended Date Table (Power Query M function)

Hi @Hesham,

Within this category you can also find a topic on creating a dynamic Start- and Enddate.

.
In addition there will also be a video on that topic in the Time Intelligence series Brian and I are doing. We’ll add a link here as soon as it’s released to the Enterprise DNA Channel.

.
Thanks for sharing your method

4 Likes

Thanks @Melissa!!!

This post came just right in time!!

:+1: :+1: :+1:

2 Likes

Hi Melissa,
The optional parameters work fine, would it be possible to add a selection parameter to show Monday as Weekday 1 please, so users can select either 0 or 1.
Paul

1 Like

Hi Paul,

Thanks for your question!

:thinking: That sounds like a nice addition indeed. So added the optional WDStartNum parameter this allows you to set the [DayOfWeek] numbering from either 0-6 or 1-7. However if omitted, the standard numbering will be 0-6.

Keep in mind that this doesn’t affect the start of the week day, that will always remain Monday as is custom to this Calendar and the ISO week logic.

3 Likes

Hi Melissa,

that’s very cool. Thank you for that.
I’d like to ask you a favour, what if the StartDate and EndDate I wanted to grab from the Table “Sales”, column “Sales Date”, using MIN and MAX?

Can you please help me with that?

Thanks.
Daniel

2 Likes

@dguedes,

Here’s a post that covers that process in detail:

In addition, @Melissa has done two videos in the time intelligence series that address creating dynamic start and end dates. You can find them here:

  • Brian
2 Likes

This really is the best date table around the web.

Every Power BI user should be using this as there core date table.

Awesome work Melissa

Sam

7 Likes

Hi Melissa

Thanks for this video — it is very good—in fact, excellent. Unfortunately, I have discovered an issue with the “Fiscal Year” Calculation.

As I am not working now, so I am using the “Adventure Works” MS Access database to build my models. As this database begins in 2001, this causes a problem when calculating the Fiscal Year. The Extended Date Table returns FY02 and FY2 as when the +1 is added if less than the FYStartMonth, it is returning the 2 without the 0 in front. If I click on FY02 and FY2 together, I get the correct results. Obviously, this only happens thru 2009 — from 2010 forward, everything works great.

I am not good at M, but I believe I did find the problem but not sure how to resolve but I will keep working on it. An M expert should be able to resolve quickly. I could solve it in excel :>) — or I will remap the data to post-2010 dates.

How many users need FY back in 2001-2009? Not many but…

Again, thanks for your contribution and you have been a great addition to Sam’s team!
Thanks
Wally

2 Likes

Hi @Wally,

Welcome to the Forum!

Must admit I new that could be an issue but forgot to fix it :innocent: It’s sorted now and I also updated the FY Offset. With some community development this is turning into a great Date table :+1:

.

Watch out for the eDNA Challenges, it’s a great way to practice with different types of data, learn and pick up new techniques others used. I hope to see some of your work there.

4 Likes

Hi @Melissa

Your update worked great ---- very fast. The “Text.PadEnd” code worked great. I will not forget that one!

I hope to join the eDNA Challenge, but while I can build some great reports, the techniques I have seen you and others use are outstanding and I need to up my game in that area. I will get there!

Thanks again for your outstanding work
@Wally

1 Like

Hi @Wally,

Great to hear and thanks for the feedback :+1:

If you have the time don’t wait to join the challenges and just jump in. There’s so much to learn from them whether you’re a beginner or a seasoned analyst… you’ll find this is a great community giving lots of feedback, tips, tricks and all sorts of support.

Here’s a link to the challenge, released yesterday. I hope you’ll reconsider and I look forward to seeing your entry :smiley:

1 Like

Hi @Melissa

Funny you should write that as I was just looking for the latest challenge. I will use it to focus my training. Do not expect much from me at the beginning, but…

Thanks for the push!
@Wally

3 Likes

Dear Forum Members,
At first I would like to thank @Melissa for the excellent M script.
I’ve immediately given it a try. It helped me very much.
The data created by that script refreshes without a problem in PB Desktop.
But the troubles have come when I’ve published my report with date table into PB Service.
The refresh there gave me an error:

[Unable to combine data] Section1/Holidays/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

That has to do something with the table of holidays which I read from the Internet and use as an option in the M script.

Could you please give me a piece of advice what to do with this error message.

Thanks in advance
Adam

2 Likes

Hi @AdamJZ,

Welcome to the forum!

.

Can you share a PBIX file with the Date- and Holiday table queries you’ve set up, also include the date range you want to cover so I can take a look?

Thanks.

2 Likes

Hello @Melissa,
Trying to prepare data for you I’ve discovered that the problem is located in my holidays table.
The holidays have to be listed for all the years from sales table.
I’ve prepared the file using your sales data from Ultimate Beginners Guide to DAX course (it’s just an example).
Example.pbix (412.7 KB)

1 Like

Hi @AdamJZ,

After taking another look at your error the answer is that you cannot combine an external data source with another query. See this article for more details and how to resolve it.

I hope this is helpful.

1 Like

Hello @Melissa,
I’ve read the article you suggested and got the idea. Thanks.
But I still have a questions.
I watched Enterprise DNA videos on date table, on setting start and end dates and on creating a holiday table. Could we set up holiday table years/dates range dynamically and then use it as optional table in the function that creates date table? (Your colleague Brian got the holiday dates from the Internet but finally created Holiday table in Excel. Does that mean that holiday table used in date table function ought to be “static”?)
Regards
@AdamJZ

1 Like

Hi @AdamJZ,

Yes but you’d have to avoid that combination of external data source with another query.
.

Not exactly, for example I created a huge Holiday table in excel and stored that on SharePoint. Now I dynamically get Holidays from that SharePoint file without having to rely on an internet site remaining active, keeping the same data structure and so on. It also avoids that external data source issue.

I hope this is helpful.

1 Like

Hi Everyone,

I would like to bring up an issue that every country has different holidays and different dates for each holiday.

Does anyone have that information for different countries?

thanks
Keith

1 Like

@Keith,

There are lots of sites on the web with this information, but one that I particularly like is this one which has all countries’ holidays listed through 2030. It is structured very similarly to the site I used in my video, so the same web scraping techniques should work there too:

  • Brian
4 Likes