topic Re: Calculate sell with DAX in DAX Commands and Tips

Calculate sell with DAX

Anonymous — Fri, 02 Aug 2024 04:31:47 GMT

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

Re: Calculate sell with DAX

Joe_Barry — Fri, 02 Aug 2024 04:48:55 GMT

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

Re: Calculate sell with DAX

Anonymous — Fri, 02 Aug 2024 05:38:43 GMT

Joe_Barry

Thanks Joe.

Please check if the links below is work

https://onedrive.live.com/?redeem=aHR0cHM6Ly8xZHJ2Lm1zL2YvYy84MTIyZjM4YzJiNzEyZGU4L0VtRzNfaGk1WVFsRnNmT1BpQk5tek80Qlg1RnRUQXZpRmVMaC1CZ0hfbkZEOGc%5FZT1CSFdXMDc&id=8122F38C2B712DE8%21s18feb76161b94509b1f38f881366ccee&cid=8122F38C2B712DE8

Re: Calculate sell with DAX

Joe_Barry — Fri, 02 Aug 2024 06:31:13 GMT

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