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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors