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

DAX DAX Calculations
1

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

YTD Invoiced =

SUMX(

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:

image

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

2

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
3

@ScottTPA,

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

YTD Invoices = 

TOTALYTD(
    [Total Invoices],
    Dates[Date]
)

image

1 Like
4

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

5

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

6

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

Thanks
Enterprise%20DNA%20Expert%20-%20Small

2 Likes
7

That worked. Thank you.