topic Re: TOTALYTD not working with year filter in DAX Commands and Tips

TOTALYTD not working with year filter

BIUser1998 — Mon, 05 Feb 2024 15:39:49 GMT

I have a data table and I'm performing YTD Calculation using the TOTALYTD Function. My year starts in June and ends in May.

I have written the following dax code which is working fine when I pass the year as 2024 but its giving me a blank value when I set the year to 2023. Can someone please guide me as to why this might be happening? Thank you for the help!

DAX Code

Total = TOTALYTD(SUM('Table'[Value]), 'Table'[Date], FILTER('Table','Table'[Year] = 2024, "05/31")

Data table

Date	Value	Year
June 2022	246	2023
June 2023	160	2024
July 2022	180	2023
August 2022	79	2023
September 2022	213	2023
October 2022	237	2023
November 2022	156	2023
December 2022	206	2023
January 2023	82	2023
February 2023	188	2023
March 2023	76	2023
April 2023	84	2023
May 2023	200	2023
July 2023	259	2024
August 2023	185	2024
September 2023	200	2024
October 2023	274	2024
November 2023	44	2024
December 2023	253	2024
January 2024		2024
February 2024		2024
March 2024		2024
April 2024		2024
May 2024		2024

Re: TOTALYTD not working with year filter

lbendlin — Tue, 06 Feb 2024 01:59:09 GMT

You MUST use TOTALYTD against your CALENDAR table, not your fact table.

Re: TOTALYTD not working with year filter

Anonymous — Tue, 06 Feb 2024 02:19:16 GMT

Hi BIUser1998 ,

To your question, here is my answer.

I put your data into Desktop and ran your DAX code and found that it didn't work, but it worked when I changed the code to the following form.

Total = TOTALYTD(SUM('Table'[Value]), 'Table'[Date], FILTER('Table','Table'[Year]))

If you change the year to 2023 it will also work.

Total = TOTALYTD(SUM('Table'[Value]), 'Table'[Date], FILTER('Table','Table'[Year] = 2023))

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: TOTALYTD not working with year filter

BIUser1998 — Tue, 06 Feb 2024 15:34:19 GMT

Hi Anonymous but if you put the measure into a KPI widget, it shows <blank> instead of showing the total

Re: TOTALYTD not working with year filter

BIUser1998 — Tue, 06 Feb 2024 15:34:44 GMT

Hello lbendlin Can you please elaborate more on this please?

Re: TOTALYTD not working with year filter

lbendlin — Tue, 06 Feb 2024 15:59:31 GMT

Time Intelligence functions like TOTALYTD must be used against the Calendar table in your data model, not the fact table. The calendar table must be contiguous (no gaps) and covering both the source and the destination of your time intelligence calculation.