Forum Discussion

TheG's avatar
TheG
Icon for Advocate I rankAdvocate I
9 years ago
Solved

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:   I now want to add the following: Financial Year (Displays as "FY 2014" would be based on 1/07/2013 to...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

TheG's avatar
TheG
Icon for Advocate I rankAdvocate I
9 years ago

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.

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • TheG's avatar
      TheG
      Icon for Advocate I rankAdvocate I
      9 years ago

      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.