Unable to change Year Start Date in Extended Dates Table

Power Query M Code Showcase
1

Hi All

I have imported the Extended Dates Table in to my model. This has created the table fine and I have set the year start date as 1 Nov and the year end date as 31 October with a wide range of years (2015-2100). This will ensure that I get the prior year history and any future years.

I then filled in the optional extra which is the start month for the fiscal year. I filled this in as 11 (November).

On reviewing my table it appears that it has still set January as the start of the fiscal year and therefore all of the quarters for the year are in relation to the calendar year.

See an extract of my dates table.

image
image937Ă—656 78.2 KB

From the table you can see that the quarter for November is 4 whereas this should be 1 if November is the first month of the calendar year.

How can I make sure that my fiscal year starts 1 November? I want to be sure that my visualizations follow the fiscal year not calendar year.

Thanks
Campbell

2

Hi @Cambo,

Just as a note, sharing your pbix when asking questions in the future will speed up people helping you with your queries.

If you scroll further across to the right, you should see columns for Fiscal Year, Period etc. It’s these ones you want to use as these are using that optional parameter.

1 Like
3

Hi Cambo, maybe you have to transform the Quarter Number column:

Table.TransformColumns(#“Renamed Columns”, {“Quarter Number”, each 5 - _}), or Table.TransformColumns(#“Renamed Columns”, {“Quarter Number”, each if _ = 1 then 4 else if _ = 2 then 3 else if _ = 3 then 2 else 1})

image
image1538Ă—596 40.5 KB

4

Are you using the EDNA Date Table?

Here is the Course that will help with that process
Dashboard (enterprisedna.co)

Fiscal year would be 11.

I hope this helps.
Keith

5

Thanks everyone for your help on this. I will have a look at the course suggested and your other replies over the break and see if I can get things working.

What a great community - 3 responses within a few hours of posting.

Cheers
Cambo

2 Likes