Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
TheG
Frequent Visitor

Adding JUNE Year Edn Financial Year reporting Columns to DATEDIM table

Hi Community,

 

I have loaded a DATEDIM table using query. My table looks like below:DATEDIM1.PNG

 

I now want to add the following:

  1. Financial Year (Displays as "FY 2014" would be based on 1/07/2013 to 30/06/2014)
  2. Financial Quarter End (Displays as "Q1FY13" would be based on 01/07/2013 to 30/09/2013, "Q2FY13" 01/10/2013 to 31/12/2014 etc...)
  3. Financial Month (July would equal Financial Month 1 instead of 7)
  4. Financial Month Short name (JUL, AUG, SEPT, OCT, etc)

I would need to do this kind of Date Dimension table often so I would like the best method to create this table and use on a regular basis.

I have searched through various posts but I get error message "when i try to add the column with the recommended formula.

 

Can anyone assist me with this request please?

 

Here is a link to my test PBI report which should make it alot easier for help.

Thanks Guys

 

https://www.dropbox.com/s/92fsrl3c65szl9v/TEST%20REPORT.pbix?dl=0

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable
Anonymous
Not applicable

Garry,

That sample Power Query script extends the Date table per this line:

#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - Date.AddDays(#date(#"Start Year",1,1),-1)), #duration(1, 0, 0, 0)),

I suggest you add an "End Year" parameter, and then amend the script line above to:

#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(#date(#"End Year", 12, 30) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),

Then, if you set your "End Year" parameter to 2025, it should extend the Date table to 31/12/2025

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

I copied the download PBI file from your link...do you have any idea how to extent the date table or have a parameter to manage the End date for the table. My project data goes from 1/07/2013 to 30/06/2025..

 

The guide doesnt use a link for the end date so it is only showing 2014 to 2017 and groups the balance in a blank time period.

 

Missing End date.PNG

 

 

Anonymous
Not applicable

Garry,

That sample Power Query script extends the Date table per this line:

#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - Date.AddDays(#date(#"Start Year",1,1),-1)), #duration(1, 0, 0, 0)),

I suggest you add an "End Year" parameter, and then amend the script line above to:

#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(#date(#"End Year", 12, 30) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),

Then, if you set your "End Year" parameter to 2025, it should extend the Date table to 31/12/2025

Brillant, it worked a treat ...thanks so much...This is the easiest way so far to create this Financial table so far..

 

The original poster gave me this advice ..

 

you can change the following line as shown below

#”Invoked FunctionSource” = Source(#date(#”Start Year”, 1, 1), Duration.Days(DateTime.Date(Date.AddYears(DateTime.FixedLocalNow(),+10)) – #date(#”Start Year”,1,1)), #duration(1, 0, 0, 0)),

Please notice above where there is the +10 this adds 10 years from today’s date.

 

i get a token Identifier expected error in regards to #duration
Here is cut and paste of the line

#”Invoked FunctionSource” = Source(#date(#”Start Year”, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow(),+10)) – #date(#”Start Year”,1,1)), #duration(1, 0, 1, 0)),

 

I will let him know your advice.

Hi Steve, thanks for this link ...I will give it a go.


I previously, did this in excel and then imported into PBI and that worked but i was looking for a quick method.

 

This looks like the way to go and has a few options I didnt have too...so thanks again for the link.!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.