topic Re: How do I get a monthly sum? in DAX Commands and Tips

How do I get a monthly sum?

stevenBD — Tue, 18 Jul 2023 20:48:01 GMT

I wish to get a sum for the values correlating for each month. Looking at the example table below, I am given a series of dates and units sold for a series of products:

Product	Date	Units Sold	Monthly Sales
Hat	May 1, 2023	21	29
Hat	May 1, 2023	8	29
Shirt	May 1, 2023	1	1
Shirt	April 1, 2023	3	8
Shirt	April 1, 2023	5	8
Shirt	March 1, 2023	14	14
Pants	March 1, 2023	4	4

The monthly sales needs to somehow add the values in the unit sold column based on the date and product column.

Re: How do I get a monthly sum?

Jihwan_Kim — Wed, 19 Jul 2023 03:26:50 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Monthly sales total: = VAR _t = ADDCOLUMNS ( SUMMARIZE ( Sales, 'Product'[Product], 'Date'[Date] ), "@unitsold", CALCULATE ( SUM ( Sales[Units Sold] ) ), "@salesprice", CALCULATE ( SUM ( 'Product Sales'[Monthly Sales] ) ) ) RETURN SUMX ( _t, [@unitsold] * [@salesprice] )