topic Re: How to sum the monthly average sales when the sales periods are different? in DAX Commands and Tips

How to sum the monthly average sales when the sales periods are different?

Young_G_Han — Thu, 04 May 2023 04:12:54 GMT

Hi.

I have a very difficult issue...

I have to sum monthly average sales of many different items sold in different periods.

Periods are from each sales start date to today.

For example,

Product Sales Start Date Transaction Date Q'ty

A 2022-01-01 2021-02-01 100

B 2022-03-12 2021-04-11 200

A 2022-01-01 2023-02-01 100

B 2022-03-12 2022-04-11 50

In the case of product A, the period from 2022-01-01 to 2023-05-04, so the average of the monthly sales I want is 200 / 17months = 11.76.

Product B, the period from 2022-03-01 to 2023-05-04, so the average of the monthly sales I want is 250 / 15months = 16.67.

I want the sum of each product's monthly average sales like 11.76 + 16.67.

Of course, I want to make a table and graphs showing the average monthly sales by product.

I already add a column in the table with the DATEDIFF( start date, today(), month) to get the period and divide the sales quantity by the month.

I could see the quantity in the table, but I could not sum the values. The visualization shows N/a as the answer.

How can I solve the problem...?

Please help.

Kannan_n — Thu, 04 May 2023 05:25:11 GMT

From my understanding, you need the sum of the product value. I have added the screenshot please loot it

I have added a new column in the Data View

Differen = 'Table'[Qty]/DATEDIFF('Table'[Sdate],TODAY(),MONTH)

this only you need

If the answer is Happy Give a Like & are satisfied with the Answer Make the question Resolved

Thanks/Regards

Young_G_Han — Thu, 04 May 2023 06:22:19 GMT

Thank you so much. I thought it would be the only solution.

Your logic is working.