Extended Date Table (Power Query M function)

[Melissa]

Hi @richmont,

I’m sure I can help you with that, could you create a new topic and provide some additional details on how the week numbers should be handled at the calendar year start and end?

Please link to this post OR @ mention me, thanks.

[Keith]

HI @richmont ,

Sorry for the confusion.

@Melissa,
I would also like to know this answer too, just in case i run up against it in the future.

thanks
Keith

[richmont]

@Melissa Thank you Melissa and for all you do! Here in the US, many companies use a standard Gregorian calendar for their “fiscal year”, including my company. Jan 1 to Dec 31.

It would be great to have a version of your date table based on this. We would define Sunday as the start of the week (not the first workday necessarily, that would still be Monday). But a week would be defined as Sunday thru Saturday. I’m sure many readers here in the US would find a Gregorian based date table useful.

Two other related things:

1. You have a weekending clause already. Could you possibly add a weekstarting one (to both versions)?
2. In the ISO code version, what does this line mean? Specifically, the last part in parenthesis.
   StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3)),

[Melissa]

Hi @richmont,

Thanks for your reply, I am away now but expect to get back to you later today, tomorrow at the latest.

[richmont]

@Melissa Perfect, thanks Melissa!

[Melissa]

Hi @richmont and @Keith,

Created a new topic, you can find it here:

Basic Date Table (Power Query M function)

Hi all, There have been requests for a basic Date table that includes week logic centered around the start- / end of week dates AND/OR fiscal weeks. Both will start on the selected Start of week day but the first creates weeks of 7 days when crossing over into the next calendar year and the second weeks with <= 7 days around each fiscal year start and -enddate, as illustrated below. . [image] . Copy this M code script into a new blank query. let fnDateTable = (StartDate as date, EndDate …

I hope this is helpful.

[Keith]

thanks @Melissa

[Melissa]

@richmont

In the ISO code version, what does this line mean? Specifically, the last part in parenthesis.
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3))

It’s actually a piece of redundant code. Formatted it makes more sence, I think.

From the inside out; I’m getting the weekday name of a specific date, keep its first 3 letters and have that start with a capital.

[richmont]

@Melissa Many thanks! One question. I am getting odd behavior with the Year & Month, Quarter & Month, and Week & Month. I have two models, one for sales, and one for quality issues. The quality issues model includes a direct query dataset (from the sales model), and it is in Mixed mode. When I use those fields on a chart visual, I get a NaN error. I do not get this error on the fully import mode model (the sales model). But I do get it on the mixed mode model (the quality dataset). It only seems to apply to those fields. I can bring in quarter, week, etc with no errors. Any idea what is causing this and how to fix?

[Jose]

Hi @Melissa was concerned about the application of the formula, provided by @sam.mckay in his article about

Showcasing Workday Number in Power BI Using DAX, to your “Date Table”?

When applying it accoding to the fields of your “Date Table” it does not seem to work and I am not able to understand what is wrong.

Thanks in advance!

[Melissa]

Hi @Jose,

Welcome to the Forum!

I’d be happy to look into this for you, can you please create a new Topic, supply your work in progress file with a mock up of the desired outcome.

See this great reference on how to do that.
https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

[Jose]

Sure! thanks How can i create a new topic, thouhght I had already created one by posting the message?

[DavieJoe]

Hey Jose,

hope all is good with you

You’ve created an additional message/post on an existing thread

If you go to this link Latest topics - Enterprise DNA Forum and select the New Topic button to create a new topic.

[Jose]

Thanks @DavieJoe

[DavieJoe]

Not a problem & welcome to the Forum

[ericet]

Hello Melissa,

In your date table, I think it’s already there but I’m not sure …

What, I’m looking for is workdays in a month.

July 2021
It had 31 days and 22 work days.

I think your Calendar has this already, can you just point me in the right direction please.

Thanks

[BrianJ]

@ericet ,

You can use the IsBusinessDay or IsWorkingDay fields to calculate this. The former is false on holidays and weekends, while the latter is false only on weekends.

With DAX, it’s a simple CALCULATE ( COUNTROWS( Dates), Dates[IsBusinessDay] = TRUE) set against the proper monthly context to give business days per month (or can substitute working days).

If you want to do this in PQ, just use an AllRows/GroupBy combo on Month & Year and IsBusinessDay or IsWorkingDay, and count the rows where the IsBusinessDay or IsWorkingDay is TRUE. This video walks you through that process:

Unleash The Potential of Group By With All Rows

I hope this is helpful.

– Brian

[ericet]

Thank you Brian, that works great. But another question in my data I don’t have all the days in a month
so I cannot count that.

Now, I’m looking at the Extended Date Table and trying to find how many days in a month ?

I’m surprised that I did not see it. Did I miss something ?

How many days in a month ?

[BrianJ]

@ericet ,

Here’s a simple way to do that without having to modify the M code. When you expand this, it will add a Days in Month column to your Dates Table.

– Brian

[ericet]

I looked at your video, but I was looking something even easier

Found this solution: (Still checking if it’s OK at first glance seems to work.)

Number of Days in a Month =
DATEDIFF(
EOMONTH(MAX(atWorkData[YYYY-MM-DD]), -1),
EOMONTH(MAX(atWorkData[YYYY-MM-DD]), 0), DAY
)