Calculate sell with DAX

[Anonymous]

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!!

[Joe_Barry]

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

[Anonymous]

@Joe_Barry 

Thanks Joe. 

Please check if the links below is work

https://onedrive.live.com/?redeem=aHR0cHM6Ly8xZHJ2Lm1zL2YvYy84MTIyZjM4YzJiNzEyZGU4L0VtRzNfaGk1WVFsRn... 

[Joe_Barry · Accepted 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