Forum Discussion
Count based on date
Hi, how can I get the total count of properties that have an expiry date within each financial year? My financial year runs 1st April - 31st March. I make it:
FY 24/25 = 4
FY 25/26 = 6
| Property_ID | Issue_Date | Expiry Date |
| 1 Lakeshore Drive | 12/08/2023 | 12/08/2024 |
| 1 Lakeshore Drive | 12/08/2024 | 12/08/2025 |
| 2 Logan Square | 07/07/2023 | 07/07/2024 |
| 2 Logan Square | 10/07/2024 | 07/07/2025 |
| 5 Giddings St | 01/02/2024 | 01/02/2025 |
| 6 Addison | 13/06/2024 | 13/06/2025 |
| 7 Ravenswood Ave | 02/08/2024 | 02/08/2025 |
| 1 Davies Road | 01/06/2024 | 01/06/2025 |
| 8 Winnemac Park | 08/08/2023 | 08/08/2024 |
| 8 Winnemac Park | 08/08/2024 | 08/08/2025 |
Hi there, assuming you do not use Date table and you are going to use the Expiry Date in this sample table. You'd need to create one Column with DAX below for FY.
Financial Year = VAR Expiry = 'CountBasedOnDate'[Expiry Date] VAR FY_EndYear = YEAR(Expiry) + IF(MONTH(Expiry) >= 4, 1, 0) RETURN "FY " & RIGHT(FY_EndYear - 1, 2) & "/" & RIGHT(FY_EndYear, 2)In reporting, it will be,
Hope it helps:)
5 Replies
RichOB The proper way to do that would be to create a date table that contains your fiscal year information. I would recommend Melissa de Korte's date table. However, there are also DAX-based date table formulas such as this one: DAX Custom 445 Calendar - Microsoft Fabric Community
Hi there, assuming you do not use Date table and you are going to use the Expiry Date in this sample table. You'd need to create one Column with DAX below for FY.
Financial Year = VAR Expiry = 'CountBasedOnDate'[Expiry Date] VAR FY_EndYear = YEAR(Expiry) + IF(MONTH(Expiry) >= 4, 1, 0) RETURN "FY " & RIGHT(FY_EndYear - 1, 2) & "/" & RIGHT(FY_EndYear, 2)In reporting, it will be,
Hope it helps:)
Hi RichOB ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you MasonMA Greg_Deckler for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
