Extended Date Table (Power Query M function)

Power Query M Code Showcase
42

@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
43

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
44

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
45

@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
46

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

Keith

1 Like
47

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

2 Likes
48

@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
49

@BrianJ,

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

Keith

1 Like
50

@Greg,

You are very welcome. :smile:

thanks
Keith

1 Like
51

@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
52

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
53

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
54

Thanks @Melissa! Amazing calendar. Love it.

1 Like
55

You’re welcome!

:+1:

1 Like
56

@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
58

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
59

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
60

Thanks @Brian!

1 Like
61

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
62

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