Forum Discussion

Lizzieleman's avatar
Lizzieleman
Frequent Visitor
2 years ago

Fiscal year in format 2023-24 for example

Hi there, I created a date table in power query and have created a fiscal year column, but it says 2023, rather than 2023-24, for example. Does anyone have M code I could try to get it in the correct format? I have added a screenshot of my date table

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Lizzieleman , Create a new column like

     

    If( Date.Month([Date]) <=3 , Number.ToText(Date.Year([Date])) & "-" & Text.End(Number.ToText(Date.Year([Date])+1),2) ,  Number.ToText(Date.Year([Date])+1) & "-" & Text.End(Number.ToText(Date.Year([Date])+2),2)  )

  • Lizzieleman's avatar
    Lizzieleman
    Frequent Visitor
    2 years ago

    I get an error

     

    "Expression.Error: The name 'If' wasnt recognised. Make sure it is spelled correctly.

    I changed it to

     

    = Table.AddColumn(#"Changed Type", "Custom", each if (Date.Month([Date]) <=3 , Number.ToText(Date.Year([Date])) & "-" & Text.End(Number.ToText(Date.Year([Date])+1),2)) , Number.ToText(Date.Year([Date])+1) & "-" & Text.End(Number.ToText(Date.Year([Date])+2),2)

    and now it says Expression.SyntaxError: Token ')' expected.

    But I can't figure out where that ) would go

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Lizzieleman ,

      In the code you provided:
      There are 13 "(" and only 12 ")". You need to add a ")" at the end of the code.
      And remember to accept amitchandak 's answer as a solution.

      Best Regards,
      Dino Tao

      • Lizzieleman's avatar
        Lizzieleman
        Frequent Visitor
        2 years ago

        Yes I tried this it still has the same error

         

        Code now reads like this:

        if( Date.Month([Date]) <=3 , Number.ToText(Date.Year([Date])) & "-" & Text.End(Number.ToText(Date.Year([Date])+1),2) , Number.ToText(Date.Year([Date])+1) & "-" & Text.End(Number.ToText(Date.Year([Date])+2),2)))

         

        But doesn't work