Forum Discussion

SantoshKumar's avatar
SantoshKumar
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

Split the CalenderDates into Finacial Years

Hi All, i have a CalendarDate Cloumn where i have all the dates for three years data.   i want to have a new column where i want to provide three values FY22,FY23 and FY24 based on the following c...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • SantoshKumar's avatar
    SantoshKumar
    3 years ago
    MahyarTF   i tried the below way and it worked for me. but appreciate your time in providing the solution. if your solution also works then i will accept yours as solution.
    FYYear1 =
    IF ( Table[CalenderDate] >= Date(2021,7,1) && Table[CalenderDate] <= Date(2022,6,30),"FY22",
    IF ( Table[CalenderDate] >= Date(2022,7,1) && Table[CalenderDate] <= Date(2023,6,30),"FY23",
    IF ( Table[CalenderDate] >= Date(2023,7,1) && Table[CalenderDate] <= Date(2024,6,30),"FY24","")))
MahyarTF's avatar
MahyarTF
Icon for Memorable Member rankMemorable Member
3 years ago

Hi,

Use below code instead of your code and see if it is working :

 IF ( 'Date'[Date] <= DATEVALUE("07/01/2021")    && 'Date'[Date] <= DATEVALUE("30/06/2022"),"FY22",
                     IF ( 'Date'[Date] <= DATEVALUE("07/01/2022")  && 'Date'[Date] <= DATEVALUE("30/06/2023"),"FY23",
                          IF ( 'Date'[Date] <= DATEVALUE("07/01/2023") && 'Date'[Date] <= DATEVALUE("30/06/2024"),"FY24")
                      )
                    )
Appreciate your Kudos and please mark it as solution if it helps you
SantoshKumar's avatar
SantoshKumar
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
MahyarTF   i tried the below way and it worked for me. but appreciate your time in providing the solution. if your solution also works then i will accept yours as solution.
FYYear1 =
IF ( Table[CalenderDate] >= Date(2021,7,1) && Table[CalenderDate] <= Date(2022,6,30),"FY22",
IF ( Table[CalenderDate] >= Date(2022,7,1) && Table[CalenderDate] <= Date(2023,6,30),"FY23",
IF ( Table[CalenderDate] >= Date(2023,7,1) && Table[CalenderDate] <= Date(2024,6,30),"FY24","")))