Cannot return correct MTD sales

[MC_2023]

Hi,

I'm comparing the MTD sales for the same period of two years, by using below measures:

Current MTD $ = TOTALMTD([Total Sales],DATESMTD(Sales[Invoice Date]))

SPLY MTD $ = CALCULATE([Current MTD $],SAMEPERIODLASTYEAR('Date'[Date]))

However, I found bug when the sku was discontinued and no sales for current month, it return a wrong sales value. 

Product Number	Current MTD $	Same Priod Last Year MTD $
764414	$         205,000	$   205,000

Correct result should be:

Product Number	Current MTD $	Same Priod Last Year MTD $
764414	$         0	$   205,000

Can anyone help on this error? Thank you!

[v-cgao-msft]

Hi @MC_2023 ,

Please try:

SPLY MTD $ =
CALCULATE (
    [Current MTD $],
    SAMEPERIODLASTYEAR ( 'Date'[Date] ),
    ALLEXCEPT ( 'Sales', 'Sales'[Product Number] )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ValtteriN]

Hi,

Try using your date table for your MTD calculation also in your base measure: 
Current MTD $ = TOTALMTD([Total Sales],DATESMTD('Date'[Date]))


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[MC_2023]

Hi, 

I amend the Current MTD $ per your suggestion, but it cannot return any figures...it's weird..

Anyway, thanks for your kind suggestion.