Hi @ezenunez
Yes I’ve been here too. Shame you cannot set a the “31/03” as a parameter somewhere an let TOTALYTD work its magic.
You need to set your date table up so that it manages fiscal years, then filter on the fiscal year in question. EDNA have an extended date table here.
Extended Date Table (Power Query M function).
I had already written mine in excel rather than power query before I saw the EDNA one which is just fantastic. So if I use my calendar the DAX below should make sense.
BINavigationCalendar.xlsx (6.6 MB).
If you put 31/03/2021 in the KeyDates tab FinancialYearEnd, the calendar will work the necessary through. The year doesn’t matter since the logic in the calendar derivation just looks at month number.
Then you need a piece of DAX like this:
VAR YTDDates = DATESBETWEEN('Calendar'[Date],max( 'Calendar'[Fisc YearStart] ),max( 'Calendar'[Date] ))
Return
CALCULATE( [YourMeasure], YTDDates )
The logic is to attack the date table and filter between Fiscal Year start and currently selected date. Fiscal Year start will show (assuming March fiscal year end) 01/04/YYYY in every date row for the fiscal year. Max(calendar[Date]) is the currently selected date.
I would recommend tweaking this with the EDNA calendar should you use it. This is because the EDNA calendar generates directly in PQ so no data upload.
Hope this helps
Pete