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
DanielButto
Frequent Visitor

Find MAX & MIN volume sales of a month from any year for a product

Hello everybody, im new to the community and excited to share some thougths, in this case I'm looking for some help.

 

The bottom line is that im looking to do a monthly forecast for the current and the next 2 months, since the product is price sensitive, I wanted to calculate an estimate base volume for the sales forecast, to do so I was thinking to stablish a future price that I would project and given this price then the returning values would be:

 

  1. MAX volume sold in a month (any historic month from the past) with the price or price range that I stablished.
  2. MIN volume sold in the month (any historic month from the past) that matches the price or price range that I stablished.

Not sure how im displaying the estimate price either, it can be just a table with prices I write down or can be calculated and adjusted with a what if parameter. So lets say there is a price of 10$, then it would look for the MAX & MIN total of historic sales in a month and bring back the volume sold of that month with the range of price 9.85$-10.15$ for example.

 

So once I have this MAX & MIN values I could use different parameters of calculation to stirr the forecast in the right direction, since im expecting that even with same prices there has been different behavior in sales.

 

My data set for this consist of 3 table: Calendar table, Product table and all historic sales data table.

 

The data table has all orders information (data, price, volume, product number, customers etc) and connects with the other 2 tables I mentioned before by date and product number.

 

My end result should be displayed in a matrix visualization with something like:

 

 image.png

 

The result in the 2 columns could of been the total sales in September of 2015, August 2016 or any other month of any other year for that product. 

 

After I would just make 3 calculations and estimate current month, next month and "in 2 month" forecast.

 

I have found very insightful material in this forums but nothing quite like this as I need the sum of the month that matches the pricing parameters for a specific product. I would much appreciate any or all help the community can offer to set me in the correct direction.

 

Regards,

 

DB

1 ACCEPTED SOLUTION

Hello @Greg_Deckler I had problem pasting data (it wouldnt upload anything I pasted in a format where you could copy/paste and have fast access to the data) combined with a poor internet is the result. Thank you for the link regarding the proper way to upload and present the information.

 

I followed many of the steps and tried to convert some parts of your post (https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...) that I found on this forums and came to solve my original problem, however now im facing a diferent problem, BUT will try to investigate some more and see if I can solve it.

 

Thank you for the replies!

 

Regards,

 

Daniel B

View solution in original post

5 REPLIES 5
v-chuncz-msft
Community Support
Community Support

@DanielButto,

 

You may try using SUMMARIZE Function inside the MINX Function and MAXX Function

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Sam,

 

You did get me thinking because at first I didnt see it, a couple of hours later it hit and seems like the way to go, but still I couldnt find a way to make it work, given the previous exmaple posted, what do you suggest? Between today and tomorrow ill keep on working on it and will update.

 

Regards,

 

Daniel B

Ive tried multiple ways with the formula I came up with. For the moment im ignoring the fact that there is a price consideration, Just attempting to bring back the Max volume from any months in the history of the company for a certain product.

 

One code that generate something close of what im looking for is:

 

 

Max = 
MAXX(Sales,
    SUMX(
        SUMMARIZE(
            Sales,
            'Product'[Consolidated Items Desc],           
            "Monthly Volume",[Volume Sold all pounds]
        ), 
    [Monthly Volume]
    )
)

 

 

But the result it yields are not what I expect. I use 2 tables to verify the information, the first one has year,month and volume displaying the information of volume by month (with subtotals in year and total for all years, picture attached) and the another table with product name and the MAX value im looking for. In this example im expecting a 56k Volume (from June 2015) but instead it brings me the total value below for all years 2.120.220, which is in fact the MAX for the measure I calculated with this context.

 

 

img1.PNG

 

Anybody able to give me some support with this please? Let me know if you need aditional information, I will provide if I can.

 

Regards,

 

Daniel B

 

@DanielButto- Been on vacation, just getting back. Can you post sample source data in a format that can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello @Greg_Deckler I had problem pasting data (it wouldnt upload anything I pasted in a format where you could copy/paste and have fast access to the data) combined with a poor internet is the result. Thank you for the link regarding the proper way to upload and present the information.

 

I followed many of the steps and tried to convert some parts of your post (https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...) that I found on this forums and came to solve my original problem, however now im facing a diferent problem, BUT will try to investigate some more and see if I can solve it.

 

Thank you for the replies!

 

Regards,

 

Daniel B

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.