Hy everyone,
I need to compare the Sales This Year with the Sales Previous Year. For this purpose I created the following measures:
This Year Sales:= SUM(Sales Amount)
Sales PY:= CALCULATE([Sales Amount], SAMEPERIODLASTYEAR('Dates Table'[Date]]
Now the problem is if I compare October 2020 to October 2019, I get This Year Sales for the Sales till the 26th of October and Sales PY is showing me the whole sales for October 2019. So I cannot compare this values. I will have the same problem by comparing YTD Data. (Today´s Date: 27.Oct 2020)
I searched on the Internet for some help and I found this Code but it is still not working:
[PY Last Day Selection]:=
VARLastDaySelection =
LASTNONBLANK ( 'Date'[Date], [Sales Amount] )
VARCurrentRange =
DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), LastDaySelection )
VARPreviousRange =
SAMEPERIODLASTYEAR ( CurrentRange )
RETURN IF (
LastDaySelection >= MIN ( 'Date'[Date] ),
CALCULATE ( [Sales Amount], PreviousRange )
i should say that if I create a pivot which shows me the months I get the wrong numbers, that is the whole sales for October 2019 BUT if i use the field dates I get the correct results. It’s showing the sales till the 26th of October 2019 but as I said at the month level it is still showing the sum of the whole month. Do I have to use FILTER to alter the row context of the total sales for October 2019?
Hi Maria,
This part of the formula:
VARLastDaySelection =
LASTNONBLANK ( 'Date'[Date], [Sales Amount] )
Should be referencing the dates in the Sales table, not the dates table. The dates table will have dates up to the end of the year. I hope that points you in the right direction.
In future, if you can't upload your actual file, please create a sample file to save me having to do so.
Thanks,
Mynda
Hi Mynda,
sorry it took me too long to answer your post. I tried what you said but I still do not getting the numbers I need. I attached a sample file.
it might be some silly mistake I am doing again but I cannot understand right now what am I doing wrong.
Thanks a lot for your help
Regards,
Maria
Hi Maria,
The formula is calculating correctly. The last value in your sales table for 2020 is on 26th October, not 28th. Also, you can't look at that measure at the day level of detail because it will ignore 2019 dates where there are no values for those dates in 2020 e.g. 3rd October, 10th etc.
Mynda
