Help with YTD Sales - Formula seems like it should work but doesn't

When I use this formula for YTD sales, I don’t get an error:

YTD Invoiced =


FILTER(all(DateTable2),DateTable2[Week Number]<WEEKNUM(today()+1)),Invoices[Total Invoiced])

This worked in 2019 because we only had a half year of 2019 data. I am trying to make this work for 2020 but when i do this I get an error:


I’d appreciate any suggestions to tweak this to recognize 2020 data and only capture that for YTD Sales Invoiced Sales.

Hi Scott,

Sam has done a great video on how to perform ToDate calculation, you can find that here:

BTW the error is caused because the DATESYTD() returns a date range and doesn’t return a scalar value inside the FILTER expression.

1 Like


It seems like it would be much simpler to use the YTDTOTAL function to accomplish what you’re trying to do:

YTD Invoices = 

    [Total Invoices],


1 Like

Melissa, thank you for the explanation and the reference to the video. I didn’t know DatesYTD() returns a range.

Brian, thank you for the suggestion. I will try this today when I get back to the project.

Try this one also. Very similar to Brian’s approach. This is how I do it in all of my models.

YTD Invoices =

CALCULATE ( [Total Invoices], DATESYTD ( Dates[Date] ) )



That worked. Thank you.