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!