Month Number Change


#1

So I am setting up a Dates table like shown in the Date Table video. The problem is an academic year is different than a sales fiscal year in that the following is true:
Month 1 is July
Month 2 is August
Month 3 is September
Month 4 is October
Month 5 is November
Month 6 is December
Month 7 is January
Month 8 is February
Month 9 is March
Month 10 is April
Month 11 is May
Month 12 is June

The date table that I created an added a column called Month Number using "Month Number = ‘Dates’[Date].[MonthNo]) does not create an opportunity for me to define the months by an academic year setup.

Any thoughts or am I thinking about it wrong?


#2

All you really need to do there is setup up an index columns. This is no different to say financial years in corporates.

You just need to make sure your month columns are sorted by this index versus any other.

Try this formula here for the index

FiscalMonthNo. = IF( MOD( Dates[MonthOfYear] + 6, 12 ) > 0, MOD( Dates[MonthOfYear] + 6, 12), 12 )


#3

I switched over to using the M Script you created in resources and had it set so that start month was equal to 7. Then I just told it to sort by the DateInt Column.


#4

Other note on this is that it still treats July as Q3 instead of Q1. I could just not use Quarters since we dont traditionally use it in higher education but it is something if possible I would like to keep just show Q1 as July, August, Sept, Q2 as October, November, December, Q3 as January, February, March, Q4 as April, May, June.


#5

Ok great that should work fine, good solution.

For the quarter you can create another calculated column and use these formulas (I do a video on this shortly)

Start with this one
FiscalMonthNo. = IF( MOD( Dates[MonthOfYear] + 6, 12 ) > 0, MOD( Dates[MonthOfYear] + 6, 12), 12 )

Then create this calculated column
FiscalQuarterNo. = INT( (Dates[FiscalMonthNo.] + 2 ) / 3 )

Then create this column
Fiscal Quarter = “Q” & Dates[FiscalQuarterNo.]

Then you should be done.

I’ll also look into the M script, as I’ve had a few requests to simplify this over time.


#6

That made the quarters as I needed them but see below. The months are sorting by alphabet within the quarters instead of by DateInt.

I can confirm I added all of the calculated columns noted.


#7

You just need to sort the MonthName column by the new Month index you created.

And complete the same for the new Quarter column. Just need to sort by column here also.

image


#8

Perfect. That means its time for me to go to bed if I missed something so basic. Thank you again its working like a charm now.