Forum Discussion
Change default fiscal year
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 FYearlet 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.
Resident RockstarI'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
FYearlet 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.
Advocate IIIf 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]))
How would you get this to show the qtr number if you have a fiscal year that starts in July?
