Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I need to pass thorugh a variable year end to TOTALYTD, however it's not working as hoped:
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
Solved! Go to Solution.
Hi @C4L84
Here is the file with the solution https://we.tl/t-LZzcXfkSG3
Your Mesure Code is
Running 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
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?
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!
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |