Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating a Fiscal Date Column

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.
 
🙂
Show and Tell
Tips and Tricks

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.