Forum Discussion
Solved
TOTALYTD using variable year end date
Hi I am using this formula for running total: No issues with this but I am also using a parameter based on month int: I need the year end date to change in the formula. I have trie...
Hey C4L84,
Can you try this? I used your pbix, and it looks like it works. I added a calculated column in your Calendar table:Calendar Year = YEAR('Calendar'[Date])
Then this measure:
Running Total Spend - Dynamic =
VAR _month = FORMAT(MAX('FY end'[Date]), "M")
VAR _day = FORMAT(MAX('FY end'[Date]), "D")
VAR _endofyear = DATE(SELECTEDVALUE('Calendar'[Calendar Year]),_month,_day)
VAR _startofyear = DATE(SELECTEDVALUE('Calendar'[Calendar Year]),_month,_day) +1
VAR _startdatedynamic = IF( MAX('Calendar'[Date]) < _startofyear, EDATE(_startofyear,-12), _startofyear)
VAR _enddatedynamic = IF( MAX('Calendar'[Date]) > _endofyear, EDATE(_endofyear,12), _endofyear)
RETURN
CALCULATE(
SUM(Sheet1[Spend]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= _startdatedynamic &&
'Calendar'[Date] <= _enddatedynamic &&
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
Compared above measure with the time intelligence DAX function, the yield same resultsLet me know how it goes.
Thanks for the suggestion Tutu_in_YYC, I tried formatting as a variable but got this error:
"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."
Unofrtunately, I am struggling to find out what this actually means and how to resolve!
Thanks for letting me know. I tried the syntax and you are right, it failed. Somehow it doesnt recognize the data type. I am looking into this.