TOTALYTD variable year

[C4L84]

Hi

I need to pass thorugh a variable year end to TOTALYTD, however it's not working as hoped: 

Running Total Spend =

VAR _EndDate = FORMAT(MAX('FY end'[Date]), "DD/MM")

RETURN

TOTALYTD(SUM('Invoiced Sales'[Spend]), 'Calendar'[Date], _EndDate)

It seems as though the FORMAT function is changing the data type to text and therefore it isn't recognised by the TOTALYTD because it interperets the text as arguement 3 - ie the filter part.

Is there a way to pass a variable date through to the year end arguement of the TOTALYTD formula?

Many thanks

[C4L84]

Hi tamerj1 - this is very almost the solution! However when the month parameter is changed to 1 the measure doesn't work as anticipated:

[tamerj1]

Hi @C4L84 
Yes I know that. Originally I used the Calendar Year Column then shifted the year one year back. But I realized that the calendar year column is just a fixed 0 value everywhere so I used the Finanicial year column instead which is not accurate but returned some results anyway. It is of if I create a new [Year] calculated column with the correct values or you willfix that from your data source and send me the updated data?

[C4L84]

I've amended the pbix to include the calendar year and made the change - it is now working! Thank you so much for your help, couldn't have done it without you. Well done!

[tamerj1]

@C4L84 
It worked with you but I was wrong. Actually I was wrong, the code had a small glitch. You can still use the financial year with no problems

Running Total Spend 1 = 
VAR _EndDate =
    MAX ( 'FY end'[Date] )
VAR _CurrentYear =
    SELECTEDVALUE ( 'Calendar'[Financial year] )
VAR _StartDate = 
    DATE ( _CurrentYear, MONTH ( _EndDate ), DAY ( _EndDate ) + 1 )
VAR _CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR _Result =
    CALCULATE (
        SUM ( Sheet1[Spend] ),
        'Calendar'[Date] >= _StartDate,
        'Calendar'[Date] <= _CurrentDate
    )
RETURN
    _Result

[tamerj1]

Hi @C4L84 

why do you need to format the date before calculating the YTD?