How do you add a Fiscal Year Column to a date table

[Anonymous]

@ links to members, content

I have a gift_date field in my table.  I created a calendar table with calendarauto(), how do I add a column with fiscal year to that calendar table?  My fiscal year starts in June and ends in July. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thanks for sharing @lbendlin , you can also try @Ashish_Mathur 's formula.

I have also create a simple sample, please refer to.

Create a column.

Fiscal Year =
VAR CurrentDate = 'Table'[Date]
VAR FiscalStartMonth = 6
RETURN
    IF (
        MONTH ( CurrentDate ) >= FiscalStartMonth,
        YEAR ( CurrentDate ) & "/"
            & YEAR ( CurrentDate ) + 1,
        YEAR ( CurrentDate ) - 1 & "/"
            & YEAR ( CurrentDate )
    )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Ashish_Mathur]

Hi,

Write these calculated column formulas in the Calendar Table

Month number = month(Calendar[Date])

Financial year = if(Calendar[Month number]>=7,year(calendar[date])&" "&year(calendar[date])+1,year(calendar[date])-1&" "&year(calendar[date]))

Hope this helps.

[lbendlin]

Don't use CALENDAR, and for sure don't use CALENDARAUTO. There is no point in doing this in DAX or Power Query, as the date information will not change and does not need to be recomputed on each semantic model refresh.

Create an external reference table that has all your fiscal date attributes.  Connect that table to your data model.