Finding the most recent instance of a month and day combination in the past

[mrlins]

Our fiscal year starts on September 1st and I want to create a measure that returns the last instance of September 1st based on today's date. I.e. if we're in June 2024, the measure should return '2023/09/01'. The steps would need to be something like:

1) Check today's date

2) Check for the most recent (Month = September) + (Day = 1) instance in the past (based on today's date)

3) Return as a date (YYYY/MM/DD)

The idea is that by referencing that measure, I can compare different dates to the start of the fiscal year (ex. days passed since the start of the fiscal year etc.).

I'm sure this is easy but I am really bad at all this and need your help 🙂

[ExcelMonke]

You're welcome - it is not the cleanest or most elegant of solutions, and I'm glad it's still getting you what you need.

In short, what this DAX code does is check whether or not today's year and date is less than the first day of the fiscal year should the fiscal year start in the same year as today's date (if that makes sense?). If it is less than, then we simply reduce the year value by 1, and if it is greater than the fiscal first day AND in the same year, it returns the first fiscal day of the year that matches the year of today's date. This is the logic flow I used at least.

Hopefully that makes sense 😅

[mrlins]

That seems to work fine! Thank you! I wish I understood exactly how it works but for now I'll just happily use the code. Thank you so much for taking the time to help!

[ExcelMonke · Accepted solution]

Hello, 
You can consider the following measure:

FirstDayFiscalYear =
VAR _YearToday =
    YEAR ( TODAY () )
    
VAR _YearCheck =
    DATE ( _YearToday, 09, 01 )
    
VAR _OverFiscalYear =
    DATE ( _YearToday, 09, 01 )
    
VAR _BelowFiscalYear =
    DATE ( _YearToday - 1, 09, 01 )
    
RETURN
    IF ( TODAY () < _YearCheck, _BelowFiscalYear, _OverFiscalYear )