Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Team,
Hi,
I have some problem with the data setting.
I have two excel files, one is inventory report downloaded from System A, which only updates the last day of each month.
Another is purchase data downloaded from System B which updates by weekly.
I need to use the inventory and the purchase data to calculate the sales for all customers (using the end-of-month inventory as the starting point, initial inventory + purchases - ending inventory = sales).
The customers and product categories in Systems A and B are not completely the same, but my sales need to cover all customers and products from both systems.
* the data links as below
https://1drv.ms/f/c/8122f38c2b712de8/EmG3_hi5YQlFsfOPiBNmzO4BX5FtTAviFeLh-BgH_nFD8g?e=BHWW07
*How can I caculate every customers every SKU sales by month?
*How can I caculate every customers every SKU sales by MOM & P3M?
thanks in advance!!
Solved! Go to Solution.
Hi @Anonymous
I have attached the PBIX.
I made some adaptions by creating dimension tables. Some of the calculated columns and Dimension tables I would have created in Power Query.
Joe
Proud to be a Super User! | |
Date tables help! Learn more
Hi @Anonymous
Access denied on the data
Before you can calculate you will need to clean the customer and product categories in Systems A & B, so that they match on both tables.
The next step would be to build you data model in a Star Schema (Link in my Bio) This means creating a table with just Customer Names and IDs and a table with Just Product names and ID. You will also need a Date Table (Link in my Bio)
Follw the Star Schema instructions, then you can start on your measures
To create a Sales measure
Total Sales = SUM('TableB'[Amount])
To get MoM you can use one measure, but for clarity, let's break it down.
Sales MTD = TOTALMTD({Total Sales], 'Calendar'[Date]) ///use the previous measure Total Sales
ou will need the previous month's value to calculate MoM
Sales PM =
CALCULATE(
[Sales MTD],
DATEADD('Calendar'[Date], -1, MONTH)
)
MoM %
MoM % =
VAR _Diff = [Sales MTD] - [Sales PM]
RETURN
DIVIDE( _Diff, [Sales PM],0)
convert then to %
Last 3 Months
Sales L3M =
CALCULATE (
[Total Sales],
DATESINPERIOD ( 'DIM Date'[Date],
MAX ( 'Calendar'[Date] ), -3, MONTH )
)
Hope this helps
Joe
Proud to be a Super User! | |
Date tables help! Learn more
Thanks Joe.
Please check if the links below is work
Hi @Anonymous
I have attached the PBIX.
I made some adaptions by creating dimension tables. Some of the calculated columns and Dimension tables I would have created in Power Query.
Joe
Proud to be a Super User! | |
Date tables help! Learn more
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |