Change default fiscal year

[Anonymous]

Hi,

I am working on a July - June Fiscal year. I have a date column. I tried using the native Fiscal year transformation on this date to get the FY, but I do not see a way to change the FY from the default calendar year. Any way to do this?

Thanks,

Scott

[Anonymous]

FiscalYear = if([Date].[MonthNo] > 6,[Date].[Year] & "-" & [Date].[Year]+1,[Date].[Year]-1 & "-" &[Date].[Year])

[greggyb · Accepted solution]

I'm curious what native fiscal year transformation you're referring to. I'm not aware of that in Power Query or DAX.

That being said, it's trivial to add a fiscal year field in Power Query as a custom field:

FiscalYear =
let
  CYear = Date.Year( [Date] )
  ,FYear =
    if Date.Month( [Date] ) > 6
    then CYear + 1
    else CYear
in
  FYear

let simply defined a local namespace for us to work in.

We assign CYear to the value of the calendar year for the date on the current row we're working on. This is only to avoid repetition of the function (which just leads to line noise). Then we assign FYear to the result of the if statement - if the (calendar) month number is > 6, then we know it belongs in the fiscal year that is 1 greater than the calendar year, else it's the same as the calendar year.

Out of our let-statement namespace, we return the value of FYear.

[brownmattc]

If you are doing this in Power BI Desktop then the formula you want is:
Fiscal Year = if(MONTH([DATE]) > 6, YEAR([DATE])+1, YEAR([DATE]))

[seanoliver]

How would you get this to show the qtr number if you have a fiscal year that starts in July?

[Anonymous]

Thanks! I actually should have said "Quarter" transformation under the date&time transform in query editor. However, this quarter seems to assume a calendar year, not a fiscal year with a july start. But your solution gets me on my way. 

Best,
Scott

[greggyb]

Fiscal quarter, simply:

FiscalQuarterNumber =
if [CalendarMonthNumber] < 4
then 3
else if [CalendarMonthNumber] < 7
then 4
else if [CalendarMonthNumber] < 10
then 1
else 2

Or you can do modulo arithmetic on the calendar quarter.

[NJ]

Hi Greg.

I see how you did fiscal year and quater but how would you do fiscal Month Jan, Feb, Mar...