Forum Discussion

UK_User123456's avatar
UK_User123456
Resolver I
7 years ago
Solved

Financial Year

Hi All,

 

How would I go about finding out the Finanacial years based on a date. My financial reporting starts on the 4th Apr and ends 31st Mar. I have a column of dates that range from 2001 to 2019, I just need to be able to find which FY dates they fall into.

 

e.g.

 

02/04/2018 would fall into FY 18/19

 

TIA

Need Help
  • evandrocunico's avatar
    evandrocunico
    7 years ago

    Hi, UK_User123456 

     

    FY = var __fy = if(ISBLANK(DimDate[Date]);BLANK(); if (MONTH(DimDate[Date]) >= 4; YEAR(DimDate[Date])&"/"&YEAR(DimDate[Date])+1;YEAR(DimDate[Date])-1&"/"&YEAR(DimDate[Date])))
    return __fy
     
    regards.

3 Replies

  • evandrocunico's avatar
    evandrocunico
    Resolver III
    7 years ago

    Hi, UK_User123456 

    Create a new column:

     

    FY = var __fy = if (MONTH(DimDate[Date]) >= 4; YEAR(DimDate[Date])&"/"&YEAR(DimDate[Date])+1;YEAR(DimDate[Date])-1&"/"&YEAR(DimDate[Date]))
    return __fy
     
    regards.
    • UK_User123456's avatar
      UK_User123456
      Resolver I
      7 years ago

      Hi, 

       

      Many thanks for this, works great, but how do I apply return blank if there is no gift date?

       

      TIA

      • evandrocunico's avatar
        evandrocunico
        Resolver III
        7 years ago

        Hi, UK_User123456 

         

        FY = var __fy = if(ISBLANK(DimDate[Date]);BLANK(); if (MONTH(DimDate[Date]) >= 4; YEAR(DimDate[Date])&"/"&YEAR(DimDate[Date])+1;YEAR(DimDate[Date])-1&"/"&YEAR(DimDate[Date])))
        return __fy
         
        regards.