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
Forget about time intelligence functions and try to do it manually. Try this
Running Total Spend =
VAR _EndDate =
MAX ( 'FY end'[Date] )
VAR _StartDate = _EndDate - 364
VAR _CurrentDate =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
SUM ( 'Invoiced Sales'[Spend] ),
REMOVEFILTERS ( 'Calendar' ),
'Calendar'[Date] >= _StartDate,
'Calendar'[Date] <= _CurrentDate
)
tamerj1 - this does work to a degree, however it excludes the year on year comparison that I need:
Here's a test pbix file set up like mine.
I have to say I admire your tenacity!
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 _ResultOk I have to admit that this the advantage of time intelligence functions 😜
However, I will try to think of something. Will get back to if I find a solution.
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!
As you can see in the top graph there are two financial years, whereas your measure only has one year
Not sure about that but you my try
Running Total Spend = VAR _EndDate = MAX ( 'FY end'[Date] ) VAR _StartDate = _EndDate - 364 VAR _CurrentDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( SUM ( 'Invoiced Sales'[Spend] ), ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ), 'Calendar'[Date] >= _StartDate, 'Calendar'[Date] <= _CurrentDate )Unfortunately this has not worked:
OK, thank you so much for your help. It's really appreciated!
Hi tamerj1 - this is very almost the solution! However when the month parameter is changed to 1 the measure doesn't work as anticipated:
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
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 problemsRunning 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