Forum Discussion

D_PBI's avatar
D_PBI
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

How to create a new formatted YYYY-YY column based on the row's date month value?

Hi,
In a single table I have a date column named 'Filing Date'. I would like to create a new column, in the same table, using the following logic.
If the Filing Date's month is 6 or greater (so 6 to 12) then in a new column (named 'Filing Year') place the current year's YYYY with the next year's YY, otherwise (so for months 1 to 5) place the previous year's YYYY with the current year's YY.

The below screenshot shows the Filing Date column with the intended Filing Year column.

I would like to achieve this using Power Query rather than DAX. I have seen M code, can't remember where now though, to achieve similar - so I do believe it is possible with a single line of M code rather than completing multple manual steps.

 

Thanks in advance.

Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi D_PBI ,
    Lot of work! I wonder if you meant 7 or greater thus splitting the year in half. If so, change the code at the beginning replacing the 6 with 7.

    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    if Date.Month ([Column1])<6 then Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))-1),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2)))},"-" ) else Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2))+1)},"-" )

     

     

6 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi D_PBI ,
    Lot of work! I wonder if you meant 7 or greater thus splitting the year in half. If so, change the code at the beginning replacing the 6 with 7.

    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    if Date.Month ([Column1])<6 then Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))-1),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2)))},"-" ) else Text.Combine({
   Text.From( Number.From(   Text.End( Date.ToText([Column1] ),4))),Text.From( Number.From(  Text.End( Date.ToText([Column1]),2))+1)},"-" )

     

     

    • D_PBI's avatar
      D_PBI
      Icon for Post Partisan rankPost Partisan
      6 years ago

      Thanks Nathaniel_C  - this is much appreciated.

      For others who may view this solution, for me, the only addition I needed to apply is to just add 'each' before the 'if' at the beginning of the M code.
      Thanks again for your speedy help and Kudos for sure 👍

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        6 years ago

        D_PBI ,

        You are welcome! It woulde have been better if I mentioned that I built this in a custom column, and the each is not required.  Power Query puts it in for you, althought it is good practice to put it in even so.
        Cheers,

        Nathaniel

         

    • Magnum_BI's avatar
      Magnum_BI
      Frequent Visitor
      3 years ago

       

      Hi,

       

      Think that when using 'Number' component, you'll hit problems with leading zeroes.  I.e. 2008/09 will appear as 2008/9.

       

      Alternate method (Australian Financial Year Jul-Jun) -

       

       
      if Date.Month([Date]) >= 7 then
      Text.From(Date.Year([Date])) & "/" & Text.End(Text.From(Date.Year([Date])+1), 2)
      else
      Text.From(Date.Year([Date])-1) & "/" & Text.End(Text.From(Date.Year([Date])), 2)
       
      Will result in 2008/09