Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@C4L84 , I doubt you can use dynamic value for that. It can take date in dd/mm too
Try like
Running Total Spend =
VAR _EndDate = MAX('FY end'[Date])
VAR _StartDate = eomonth(_EndDate,-12)+1 //or// date(year(_EndDate)-1, month(_EndDate) , day(_EndDate)+1)
RETURN
calculate(SUM('Invoiced Sales'[Spend]), filter('Calendar','Calendar'[Date] >=_StartDate && 'Calendar'[Date] <= _EndDate ))
or
Running Total Spend =
VAR _EndDate = MAX('FY end'[Date])
VAR _StartDate = eomonth(_EndDate,-12)+1 //or// date(year(_EndDate)-1, month(_EndDate) , day(_EndDate)+1)
RETURN
calculate(SUM('Invoiced Sales'[Spend]), filter(all('Calendar'),'Calendar'[Date] >=_StartDate && 'Calendar'[Date] <= _EndDate ))
Thank you for your suggestions amitchandak, however neither of them work:
1.
2.
I've uploaded a test pbix to dropbox here
Hi tamerj1 - it's easier to understand
Then can you try without the time format?
I'm sorry, I don't understand... I'm not formatting the time - it's formatted dd/mm day/month eg 22/03
@C4L84
Ok. Then I guess you mean to say that the date has text data type and this is the source of the error. Usually you should check and fix data type of all columns in power query before loading the data. However, if this is not what you want to do and if you don't want to create a new column in the 'FY End' table with the correct data type date then you can use the following code. Hopefully, DAX will automatically convert the string numbers into integers:
Running Total Spend =
VAR _EndDate =
MAXX (
ADDCOLUMNS (
'FY end',
"@Date", DATE ( 1, RIGHT ( 'FY end'[Date], 2 ), LEFT ( 'FY end'[Date], 2 ) )
),
[@Date]
)
RETURN
TOTALYTD ( SUM ( 'Invoiced Sales'[Spend] ), 'Calendar'[Date], _EndDate )
If the error remains then use this one
Running Total Spend =
VAR _EndDate =
MAXX (
ADDCOLUMNS (
'FY end',
"@Date",
DATE ( 1, INT ( RIGHT ( 'FY end'[Date], 2 ) ), INT ( LEFT ( 'FY end'[Date], 2 ) ) )
),
[@Date]
)
RETURN
TOTALYTD ( SUM ( 'Invoiced Sales'[Spend] ), 'Calendar'[Date], _EndDate )
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:
@C4L84
You need to check your local settings. Try insert dates manually untill you get it right with no error.
It works fine when the date is added manually, so I don't think it is because of the local settings.
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!
Can you please explain further what is meant by "excludes the year on year comparison"??
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 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.
OK, thank you so much for your help. It's really appreciated!
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
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |