Creating a Column with Number for Fiscal Month

[Anonymous]

I'm trying to add a column so I can do two things.

1st - Add column to get the number for each fiscal month

2nd - Add column to sort by fiscal month

I used the time function to create a column using:

= Table.AddColumn(#"Changed Type", "Number of Fiscal Month", each Date.Month([Document Date.Day]))

But this gives me calendar number (Eg. January = 1, December = 12)

I tried = Table.AddColumn(#"Changed Type", "Number of Fiscal Month", each Date.Month([Document Date.Day]+3))

Which resulted in January = 4 and December = 15, but I need January = 4, December = 3

Tried an If statement, 

IF( Date.Month([Document Date.Day]))<=9, Date.Month([Document Date.Day]))+3, Date.Month([Document Date.Day]))-3)

But that doesn't work.

I could do a replace value for each, but there must be a better way.

[Greg_Deckler · Accepted solution]

@Anonymous I think you want:

IF( Date.Month([Document Date.Day]))<=9, Date.Month([Document Date.Day]))+3, Date.Month([Document Date.Day]))-9

[Anonymous]

I think my problem was where I was building the formula.  I was working inside Transform Data and Add a Column.  For some reason it wouldn't let me add the formula there, but then I went into the Table View and Add a Column and it worked from there.

[v-jingzhang · Accepted solution]

Hi @Anonymous , try this: 

= Table.AddColumn(#"Changed Type", "Number of Fiscal Month", each if(Date.Month([Date])<=9) then Date.Month([Date])+3 else Date.Month([Date])-9, Int64.Type)

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[Greg_Deckler · Accepted solution]

@Anonymous I think you want:

IF( Date.Month([Document Date.Day]))<=9, Date.Month([Document Date.Day]))+3, Date.Month([Document Date.Day]))-9