Creating a Fiscal Date Column

[Anonymous]

Hi all,

I have seen a lot of infomation about creating Fiscal Year columns. But I have not seen anything about creating Fiscal Date Column.

So the constraints are on this sitauation where:

A date table that was based on the max and min dates in the model

A date table that contains Fiscal Date column.

Calanderauto(7) was not working and returned all dates.

My current solution is:

New Calander/Date table

DateTime = CALENDAR(min('Order'[Date]),max('sales'[Date]))

FinancialDate = if(DateTime[DateTime].[MonthNo]<7,FORMAT(DateTime[DateTime], "dd/mm/yyyy"), FORMAT(DATE(DateTime[DateTime].[Year]-1, DateTime[DateTime].[MonthNo],DateTime[DateTime].[Day]), "dd/mm/yyyy"))

However, this version above has an issue with month order. So;

Financial Date = if(DateTime[DateTime].[MonthNo]<7,FORMAT(DATE(DateTime[DateTime].[Year], DateTime[DateTime].[MonthNo]+6,DateTime[DateTime].[Day]), "dd/mm/yyyy"), FORMAT(DATE(DateTime[DateTime].[Year]-1, DateTime[DateTime].[MonthNo]-6,DateTime[DateTime].[Day]), "dd/mm/yyyy"))This will work fine with month order.

This still has an issue with month name. So I would not use it for display. But should be fine for relative date slicing and the like.

I would be interested to know if there is an M/Power Query version.

🙂

[Greg_Deckler]

Sorry, what is the difference between a date column and a fiscal date column? Can you show me visually one versus the other?

[Anonymous]

Thank you greg for responding!

The basic idea was that of having a regular date column. But with based around a fiscal year. I.e. The year starts in month 7 or 4 etc.

This would enable all the regular features that come with a Date column but with fiscal year as the basis.

Although recently, I have possibly concluded that it may be a waste of time and just using regular date for drilling, with a fiscal date category seperate for slicing, is probably fine and good enough.