Sum with condition

[cyuan]

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

[Jihwan_Kim · Accepted solution]

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] )

[Anonymous · Accepted solution]

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.

[Jihwan_Kim · Accepted solution]

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] )

[cyuan]

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

[cyuan]

Hi Tom,

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