Extended Date Table (Power Query M function)

@Greg,

While this sort of effort is never wasted, I think everything except the text DayType can already be done off the existing IsWorkingDay field.

  • Brian
1 Like

Hi @Greg,

The InsertIsWeekday / InsertIsWeekend is covered by the IsWorkingDay column which returns TRUE for weekdays and FALSE for weekends.

However DayType and IsBusinessDay are not, I’ve added them to the code.

Thanks for sharing!

3 Likes

I guess how do you classified “IsBusinessDay” it all depends on the kind of business you are involved in. example most retail businesses operate 7 days a week so every day is a “IsBusinessDay”.

thanks
Keith

1 Like

@Keith,

In that case you could just express it as ‘Dates’[DayOfWeek] >= 0 (or just not filter day of the week at all).

  • Brian
2 Likes

@BrianJ
very true …i was just raising it as information too :smile:

Keith

1 Like

Mea culpa … I’ve been consulting to the government too long … thanks for the reminder.
Greg

2 Likes

@Keith,

Retail/sales always seem to be the odd duck in the time intelligence pond. I’d never even heard of a 4-4-5 calendar or a 13 period fiscal calendar before I started participating on this forum.

  • Brian
1 Like

@BrianJ,

I guess we all learn something every day. :slight_smile: Everyone has different experiences in personal or career.

Keith

1 Like

@Greg,

You are very welcome. :smile:

thanks
Keith

1 Like

@Melissa… I agree with you that some of the coding will not apply to every situation. having a structure is the important thing.

thanks
Keith

2 Likes

Hi all,
Which column do you sort Quarter & Year by to get the correct sort order?
I have tried a few and get an odd error. I don’t quite understand how dateint could have more than one value for each dateint.
Capture

Thanks for any assistance.
John

1 Like

Hi @jgriffit,

For Sort by to work you need to identify a column at the same granularity as the column you want to sort, in this case that will be either QuarternYear OR QuarterOffset columns.

I hope this is helpful

2 Likes

Thanks @Melissa! Amazing calendar. Love it.

1 Like

You’re welcome!

:+1:

1 Like

@jgriffit,

Just FYI for future reference - I did a video on this topic last week that provides a general approach to sorting even the most difficult combinations of columns in the extended date table.

  • Brian
4 Likes

Hi @surpat. I’d suggest you remove this post and create a new topic … posting a reply to a solved thread will reduce its visibility and may lead to it taking longer for forum members to find … you can link to @Melissa’s thread instead. Please also provide your work-in-progress PBIX file and a mockup and/or Excel presentation of your desired outcome.
Greg

1 Like

Hi @surpat,

For Time Intelligence functions to work there are some requirements, you can find them in this recent post.

Do I really need a full year in my Date table? - #2 by Melissa

Alternatively I would suggest not creating a relationship between the Date- and Fact table but using TREATAS to create that relationship virtually since the granularities between the tables don’t match.

As @Greg mentioned, if you have need of further assistance please create a new thread and provide all relevant information and details.

1 Like

Thanks @Brian!

1 Like

As ever, Melissa, you take the time to explain, step by step, what and why and how. The date table as a query is wonderful and all goes to help us understand M code better. Thanks so much. Thanks to you and everyone and E-DNA, I’m moving up the ladder, rung by rung!

3 Likes

Hello Melissa,

Awesome script, but I need it in the french language. Any suggestion for me ?
I just need to translate the text nothing else.

Should I just translate the text in “qoutes” and that should work ?

Thanks

Eric (Montreal, Canada)

1 Like