Forum Discussion

sportyarod's avatar
sportyarod
Regular Visitor
1 year ago
Solved

How to change fiscal year coding

I have taken over a file that already had a default calendar table.  My company's fiscal year starts on 4/1 each year and am looking to make adjustments to the calendar table to reflect this.  Right now coding is 

Fiscal Year",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13))
 
How would I update this to start the FY in April and how can I change to have the data show as FY159 instead of 2024?
Calendar Table
DAX
Fiscal Year
  • MasonMA's avatar
    MasonMA
    1 year ago

    Hi sportyarod 

     

    If your fiscal year starts in April, then i suppose _startOfFiscalYear should equal 4 . but I'd suggest you just use below measure,

    FiscalYearNum = 
YEAR([Date]) + IF(MONTH([Date]) < 4, -1, 0)

    to continue to get the offset Fiscal year, use below measure.

    FiscalYear = "FY" & FORMAT([FiscalYearNum] - 1865, "000")

     

    Thanks 

    Mason

3 Replies

  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    1 year ago

    Hi sportyarod 

     

    If your fiscal year starts in April, then i suppose _startOfFiscalYear should equal 4 . but I'd suggest you just use below measure,

    FiscalYearNum = 
YEAR([Date]) + IF(MONTH([Date]) < 4, -1, 0)

    to continue to get the offset Fiscal year, use below measure.

    FiscalYear = "FY" & FORMAT([FiscalYearNum] - 1865, "000")

     

    Thanks 

    Mason

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    Hi sportyarod please try this

     

    Fiscal Year =
    VAR FiscalStartMonth = 4
    VAR FiscalYearEnd = IF(
    MONTH('date'[Date]) >= FiscalStartMonth,
    YEAR('date'[Date]) + 1,
    YEAR('date'[Date])
    )
    RETURN
    "F" & (FiscalYearEnd - 1865)

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi sportyarod,

    Thanks for reaching out to the Microsoft fabric community forum.

    It looks like you want to make changes in the calender table. As techies and MasonMA both have responded to your query, kindly go through the response and check if it solves your issue.

     

    I would also take a moment to thank techies and MasonMA, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.