topic Re: Cannot return correct MTD sales in DAX Commands and Tips

Cannot return correct MTD sales

MC_2023 — Thu, 11 May 2023 05:08:40 GMT

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

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!

Re: Cannot return correct MTD sales

ValtteriN — Thu, 11 May 2023 06:52:46 GMT

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/

Re: Cannot return correct MTD sales

MC_2023 — Thu, 11 May 2023 07:39:03 GMT

Hi,

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

Anyway, thanks for your kind suggestion.

Re: Cannot return correct MTD sales

Anonymous — Fri, 26 May 2023 05:52:53 GMT

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