Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MC_2023
New Member

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 3
v-cgao-msft
Community Support
Community Support

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
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi, 

 

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

Anyway, thanks for your kind suggestion.

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors