topic Re: Sum with condition in DAX Commands and Tips

Sum with condition

cyuan — Tue, 25 Jun 2024 01:10:25 GMT

Hi all,

I’d like to create a sum by quarter but only for month age greater than 3.

in below case, the first condition is to look at month age, as long as it is greater than 3, then sum from very first value in sales (Nov 2023 in this case)

below is what I used however it didnt return me Nov and Dec 2023 as they are below month age 3 level.

N_Sales = if(max('Sales'[MonthAge])<=3,blank(),[Sales])

thanks in advance

Bizkey	Sales	Date	Month Age
AAA333	2	1/11/2023	1
AAA333	5	1/12/2023	2
AAA333	2	1/01/2024	3
AAA333	1	1/02/2024	4
AAA333	6	1/03/2024	5
AAA333	8	1/04/2024	6
AAA333	10	1/05/2024	7

Re: Sum with condition

Jihwan_Kim — Tue, 25 Jun 2024 02:50:56 GMT

Hi,

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

expected result: = VAR _t = FILTER ( Sales, CALCULATE ( DATEDIFF ( MAX ( Sales[Date] ), TODAY (), MONTH ) ) > 3 ) RETURN SUMX ( _t, Sales[Sales] )

Re: Sum with condition

Anonymous — Wed, 26 Jun 2024 06:22:48 GMT

Hi cyuan ,

Thank you Jihwan_Kim very much for the solution, and I've tried other ways to help you understand the problem:

I guess what you mean is that you want to ask for the value of sales under your selected month age > 3. Here's a bit of advice for you:

When you use the algorithmic logic you have shown, you need to make sure that it is used in a calculated column and that sales cannot be a value calculated by measure.

Column = if(('Table'[Month Age])<=3,blank(),[Slaes])

You change to the new data source you need to use in the following actions.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Sum with condition

cyuan — Sun, 30 Jun 2024 22:57:27 GMT

Hi Tom,

Thanks for reply. Technically I used the a similar approach which also returns correct values.

Re: Sum with condition

cyuan — Sun, 30 Jun 2024 22:58:18 GMT

Thanks Jihwan, i've tried SUMX and it works too. much apprecaited.