Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Change default fiscal year

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 t...
  • greggyb's avatar
    greggyb
    10 years ago

    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.

greggyb's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

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's avatar
    brownmattc
    Icon for Advocate II rankAdvocate II
    10 years ago

    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's avatar
      seanoliver
      Regular Visitor
      9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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's avatar
      greggyb
      Icon for Resident Rockstar rankResident Rockstar
      10 years ago

      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's avatar
        NJ
        New Member
        9 years ago

        Hi Greg.

         

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