Forum Discussion
SantoshKumar
Microsoft Employee
Microsoft EmployeeSplit 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 conditions.
IF ( Table1[CalenderDate] <="7/1/2021" && Table1[CalenderDate] <="6/30/2022","FY22",
IF ( Table1[CalenderDate] <="7/1/2022" && Table1[CalenderDate] <="6/30/2023","FY23",
IF ( Table1[CalenderDate] <="7/1/2023" && Table1[CalenderDate] <="6/30/2024","FY24"
when i apply above condition it is throwing out the error as "DAX Comparision does not support comparing values of type Date with text.
Any help is appreciated.
CalenderDate Column
| 11/1/2021 |
| 10/1/2021 |
| 9/1/2021 |
| 8/1/2021 |
| 7/1/2021 |
| 6/1/2021 |
| 5/1/2021 |
| 12/1/2021 |
- 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","")))
2 Replies
MahyarTFMemorable Member
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
SantoshKumarMahyarTF 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","")))