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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sagadgreat
Helper I
Helper I

Tracking sales vs. returns made in consecutive months

I have a table where there are sales vs. returns made in consecutive months anda  formula that is calculating total extended sales. 

 

If I put both months in using the positive and negative qtys (see table example), it works fine to remove or zero out the item from total sales as being returned.  If I filter to February however, I get the -revenue with no cost associated, skewing calculations for gross margin, etc.

 

My thought is for the following month, when that return occurs it should zero out the previous sale for both cost and total revenue, removing it from the equation for Gross Profit and Margin calculations.  I have tried both multiplying by the negative qty and making absolutes but just can't seem to get it to work.

 

I am a newbs newb and appreciate any guidance as this is my first post to the community:

 

Customer NameDateQty PurchasedCostTotal Revenue
ABC Company1/12/2020110100
ABC Company2/12/2020-110-100

  

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

I did not get it completely. But do you want to combine this month's sales with next month's return? you can have formula's like

 

MTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
next MTD (complete) return =  CALCULATE(SUM(Return[Return Amount])*-1,DATESMTD(ENDOFMONTH(dateadd('Date'[Date],1,MONTH))))

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @sagadgreat 

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
 
Best Regards
Maggie
amitchandak
Super User
Super User

I did not get it completely. But do you want to combine this month's sales with next month's return? you can have formula's like

 

MTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
next MTD (complete) return =  CALCULATE(SUM(Return[Return Amount])*-1,DATESMTD(ENDOFMONTH(dateadd('Date'[Date],1,MONTH))))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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