Grand Total not working for sales per day last year function

0
votes

I am trying to do a sales by day comparison where I compare the sales this year with the sales of the same day last year as a date of the week.

So I would be comparing Monday March 25 2019 with Monday march 24 2018 etc

Here is the formula I’m using for last year’s sale

Amount per Day LY = CALCULATE([Amount TY], FILTER(all(Dates), Dates[Date] = MAX(Dates[Date])-364))

However, my total isn’t working right for my sales last year. It will just be the total for 1 day (and that day seems to change as my date range increases)

enter image description here

2
powerbidax

2 Answers

0
votes

Because you are having a one to one, you can do this with LookupValue:

SalesLY = LOOKUPVALUE(Sheet1[Sales];Sheet1[Date];DATEADD(Sheet1[Date];-364;DAY))

However if you have more rows for the same date, this will not hold, in this case you need to sum all the dates together,

SalesLY = CALCULATE(SUM(Sheet1[Sales]);FILTER(Sheet1; DATEADD(Sheet1[Date];364;DAY)= EARLIER(Sheet1[Date])))
0
votes

Using DateAdd fixed my problem

Amt per Day LY = CALCULATE([Amount TY], DATEADD(Dates[Date], -364,DAY))