Creating a Column with Number for Fiscal Month

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.

Anonymous I think you want:

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

Community Champion

Anonymous I think you want:

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

v-jingzhang
Community Support
5 years ago
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.
Anonymous
Not applicable
5 years ago
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.

Forum Discussion