Calculate the sum of the last occurrence of a column for each distinct value of another column

[Omardakkak9]

Hello everyone,

I want to calculate the sum of the "valr_indc" column, keeping just the values for the maximum date column "id_dat" for each value of the column "id_cont".

Here is the original table "fact_table" : 

id_row	id_cont	id_dat	valr_indc
1	1	26/10/2022	400
2	1	21/10/2022	800
3	2	02/10/2022	900
4	3	20/10/2022	200
5	2	26/10/2022	200
6	2	25/10/2022	300
7	2	27/09/2022	1200
8	1	02/09/2022	1500
9	3	01/01/2022	800
10	1	11/10/2022	100
11	3	02/10/2022	1000
12	3	25/10/2022	600
13	1	24/10/2022	700

In theory I will keep just one row for each id_cont which is equivalent to the max date row :

id_row	id_cont	id_dat	valr_indc
1	1	26/10/2022	400
5	2	26/10/2022	200
12	3	25/10/2022	600

Then what I want is the sum of the valr_indc column, which is 400 + 200 + 600, which gives us 1200.
I want the DAX formula to do so,

Thanks in advance!

[v-easonf-msft · Accepted solution]

Hi, @Omardakkak9 

My method will be a bit cumbersome.

Please try formula like:

id_row_latest = 
CALCULATE (
    MAX( fact_table[id_row] ),
    FILTER (
        fact_table,
        fact_table[id_dat] = MAX ( fact_table[id_dat] )
            && fact_table[id_cont] = MAX ( fact_table[id_cont] )
    )
)

latest_date = MAX(fact_table[id_dat])

latest_valr_indc = 
CALCULATE (
    MAX ( fact_table[valr_indc] ),
    FILTER (
        fact_table,
        fact_table[id_dat] = MAX ( fact_table[id_dat] )
            && fact_table[id_cont] = MAX ( fact_table[id_cont] )
    )
)

sum of valr_indc = 
VAR tab =
    SUMMARIZE (
        fact_table,
        fact_table[id_cont],
        "row", [id_row_latest],
        "date",[latest_date],
        "var_indc", [latest_valr_indc]
    )
RETURN
    SUMX ( tab, [var_indc] )

Best Regards,
Community Support Team _ Eason