topic Re: TOTALYTD variable year in DAX Commands and Tips

TOTALYTD variable year

C4L84 — Mon, 21 Mar 2022 16:36:54 GMT

I need to pass thorugh a variable year end to TOTALYTD, however it's not working as hoped:

Running Total Spend =

VAR _EndDate = FORMAT(MAX('FY end'[Date]), "DD/MM")

RETURN

TOTALYTD(SUM('Invoiced Sales'[Spend]), 'Calendar'[Date], _EndDate)

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

Re: TOTALYTD variable year

tamerj1 — Mon, 21 Mar 2022 18:51:21 GMT

Hi C4L84

why do you need to format the date before calculating the YTD?

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 09:38:10 GMT

Hi tamerj1 - it's easier to understand

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 09:46:54 GMT

Then can you try without the time format?

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 09:50:05 GMT

I'm sorry, I don't understand... I'm not formatting the time - it's formatted dd/mm day/month eg 22/03

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 10:21:26 GMT

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 )

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 10:29:36 GMT

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."

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 11:06:05 GMT

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

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 11:14:46 GMT

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:

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

Re: TOTALYTD variable year

amitchandak — Tue, 22 Mar 2022 13:00:14 GMT

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 ))

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 ))

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 13:05:43 GMT

C4L84
You need to check your local settings. Try insert dates manually untill you get it right with no error.

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 14:22:41 GMT

It works fine when the date is added manually, so I don't think it is because of the local settings.

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 14:31:21 GMT

Thank you for your suggestions amitchandak, however neither of them work:

I've uploaded a test pbix to dropbox here

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 15:06:45 GMT

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 )

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 15:25:44 GMT

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!

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 15:35:34 GMT

C4L84

Can you please explain further what is meant by "excludes the year on year comparison"??

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 15:42:18 GMT

As you can see in the top graph there are two financial years, whereas your measure only has one year

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 15:47:47 GMT

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 )

Re: TOTALYTD variable year

C4L84 — Tue, 22 Mar 2022 15:52:53 GMT

Unfortunately this has not worked:

Re: TOTALYTD variable year

tamerj1 — Tue, 22 Mar 2022 15:58:30 GMT

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.