Extended Date Table (Power Query M function)

Power Query M Code Showcase
228

@Melissa
Hello Melissa! Is there any way to modify this so that the Fiscal Week starts in the stated date regardless of the weekday, and contains 7 days in the first week of the FY?

229

Hello Forum,

I am new. How I can post a topic on the Power Query Category?

Thanks :smiley:

230

Hi @Melissa !
Thanks for a great date table. Are there any plans for adding Tertial columns into the base version or should I make a try on my own :grimacing: ?

Thanks

231

Hi @CATZ.

Firstly, please do not post to solved threads. Rather, start a new thread and link to a solved thread if it pertains to your issue. This will ensure visibility for your issue and is also for your benefit, as many forum members do not view solved threads.

@EnterpriseDNA, please split this thread into its own thread starting with the post above this one.

Secondly, an explanation and example showing your issue would help the forum members pursue your issue. I’m not sure to what you’re referring by “tertial” columns; I, for one, have not encountered the word “tertial” in 5 years of using Power BI.
Greg

1 Like
232

Hi !
OK, will do.
By the way, Tertial = 4 months
// CATZ

233

Hi!

Is there away to modify the code a bit so the end year is updated automatically?
I set and run the Query with start and End date. The Table generated fits my need.

However, when the end date is passed I need to adjust the end year manually.

= Query1(#date(2020, 1, 1), #date(2024, 12, 1), null, null, 1, null)

I was looking for something like…
= Query1(#date(2020, 1, 1), #date( YEAR(TODAY(), 1, 1), null, null, 1, null)
…but this is not working at all of course.

Thanks in advance

234

Hi @Carl_Hellberg

I recommend creating a new thread in future, as often people don’t look at closed threads.

Try replacing your YEAR(TODAY()) part with this:

Date.Year(DateTime.LocalNow())

Giving you the full query of:

Query1(#date(2020, 1, 1), #date(Date.Year(DateTime.LocalNow()) , 12, 1), null, null, 1, null)

YEAR() is a dax function, not m query for reference.

You can read more about date functions in m query here

1 Like
235

Thanks, it worked as I wanted, I will follow your recommendation in the future!

236

I am looking at a problem now where I need to know the number of workdays in a month and which day today is.

E.g. April 2025 has 22 work days in it (lets not get hung up on Easter etc.) and the 15th of April 2025 is workday number 11.

Sam has done a video on how to do this is DAX, but it seems to me that it would be better inside the date table.

Is that possible?

Thank you

237

Hi Melissa, carlos here :raised_hand_with_fingers_splayed::wink:! late to the Party here…and question, would it be possible to have ISO month on this Gem Calendar on yours?