File 1 - Contains Sales Data
Item No - Location - Qty - Rate Value
File 2 - Stock Report
Item No - Location - Qty - Rate Value
Wanted to create the Matrix to find the sales for last 6 month sale based on stock available in Store on the basis of Item No and Location.
Pls help
The CALCULATE function will likely be able to do this, but I won’t be able to really help without a file to work with.
FgStock.xls.xlsx (10.0 KB)
Item.xlsx (8.0 KB)
Sales Report.xlsx (9.4 KB)
Test.pbix (77.0 KB)
I’m a bit confused about what you’d like to final outcome to be. Can you mock up the results you’re looking for using a SUMIFS in Excel?
to extract Qty sold on the basis location and item code ( From Sales Register) against the stock avaible in store (Stock Report)[](https://).
You can either use CALCULATE or FILTER Functions. You can use RELATED to bring in columns from other tables.
data (1)Check.csv (291 Bytes)
Test-PG1.pbix (95.8 KB)
The data is not available for last 6 months so I was not sure if it was for last 6 months from today or any date you choose from the slicer. I have added options for both. If you meant last 6 months from today, you can use the Relative date slicer and if you meant last 6 months from a particular month from calendar, you can use the Calendar list slicer.
Changes made by me:
Your model did not have a calendar so I had to add a calendar table in Power Query
I consolidated Stock and Sales values into a single consolidated table using Append and Merge in Power Query.
I created Measures for Sales and Stock such that they reflect total consolidated values irrespective of what is chosen on the Date slicers.
I created last 6 months Sales measure to reflect Sales for past 6 months dependent on the month chosen on the slicers as I was not sure if you meant a relative date or absolute date in terms of 6 months period.
Thanks a lot for your effort to solve the issue,
Many Thanks.
Just found another video which you might be able to adopt to your last 6 months requirements.
Hi @PKM251970, did the response provided by the community members help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Thanks
I got the soln
