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 t...
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)},"-" )

     

     

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)},"-" )

 

 

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