Forum Discussion
TOTALYTD variable year
Hi C4L84
Here is the file with the solution https://we.tl/t-LZzcXfkSG3
Your Mesure Code isRunning Total Spend = VAR _EndDate = MAX ( 'FY end'[Date] ) VAR _CurrentYear = SELECTEDVALUE ( 'Calendar'[Financial year] ) VAR _StartDate = DATE ( _CurrentYear, MONTH ( _EndDate ), DAY ( _EndDate ) ) VAR _CurrentDate = MAX ( 'Calendar'[Date] ) VAR _Result = CALCULATE ( SUM ( Sheet1[Spend] ), 'Calendar'[Date] >= _StartDate, 'Calendar'[Date] <= _CurrentDate ) RETURN _Result
The data type of 'FY End'[Date] is date, not text.
The TOTALYTD formula will not accept dd/mm/yy format for the year end arguement, so I need to drop the year from 'FY End'[Date].
I attempted to do this using FORMAT but I think it changes the data type from date to text.
I used your suggested expression but had this:
"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."
C4L84
The last argument is the last date of your fiscal year. This is usually a fixed number. But who said you need to drop the year from 'FY End'[Date]? This should be a proper date. Am I missing something
In this instance the last arguement is not a fixed number, I have a parameter in the pbix that changes the 'FY End'[Date].
The year portion isn't required in the TOTALYTD year end arguement: