Extended Date Table (Power Query M function)

Hi Pedro,

As a matter of fact @BrianJ and I are collaborating on a Time Intelligence series as we speak and of course we will also be covering the Date table. So if you haven’t already, subscribe to the Enterprise DNA YouTube channel so you won’t miss a thing! :wink:

4 Likes

oooohhhh that is soooooooo cooooll!!! and yes i am a dna youtube subscriber for quite a while now. And it’s a great idea to teach also about TI measures concerning last week or weeks or month or quarter regarding the beginning of the year.
Thanks a lot Melissa. That is great news!!!

1 Like

Muchas gracias Melisa, Awesome

1 Like

This is very helpful Melisa, you are a rockstar here.

1 Like

@Melissa I need to adjust the ISO Year column to match our “week-years.”
Our weeks run Sunday - Saturday, and our billing “months” end on the last Saturday in the calendar month.
I have been trying to adjust the code in the InsertWeekNumber= line to match this calendar. Can you explain out the last ‘else’ line in that code?
else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Sunday)+1)+10)/7), type number),
Specifically - Why is it adding 1, then 10 before dividing by 7?
Learning a ton here. . .
Thank you.

1 Like

Hi @kjssdca,

Thanks for your question.

By default the Power Query Date.DayOfWeek function returns a number between 0 and 6 starting on Sunday. To return the weekday number according to ISO8601 rules, you have to add the optional first-weekday value (= Day.Monday) and change the numbering so it runs between 1 and 7.
This explains the +1

The +10 / 7 is part of the algorithm for calculating the week number, you can find more on that here.

If you need assistance creating a customized week number, please create a new topic. You’ll find that there’s an increasing number of active members here who’d like to help out. :wink:

2 Likes

Thank you @Melissa for sharing! Date Table is getting better and better :+1:

Your code has opened my appetite to keep the “EndDate” dynamic, so it will always be the last day of the current year. That would save me from updating/maintaining the Dates table in all my reports next year(unless there is another way which I am not aware of :smiley: )

Using your M code, I have:
1- Removed “EndDate as date” from the first line and;
2- Added another line “EndDate = Date.EndOfYear(CurrentDate),” after the “Current Date”.

2 Likes

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