Forum Discussion

MC_2023's avatar
MC_2023
Regular Visitor
3 years ago

Cannot return correct MTD sales

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!

 

 

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      MC_2023
      Regular Visitor
      3 years ago

      Hi, 

       

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

      Anyway, thanks for your kind suggestion.

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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