using DAX to calc the sum, whilst excluding the first registered amount of liters

[WTsrnl]

Hi all,

I need a bit of help. I couldn't find any other threads that could help me with this, and if you do, i'd love to know about it.

I need to build a DAX measure that calculates the average amount of liters used / person / km. But whilst calculating this, I need to exclude the amount of liters used on the first date.

I tried to find the first date per person, and exclude the liters registered from the sum of total liters per person. After that i can calculate the average km/liter. I just can't find the way to find the first date per person.

If you have any questions, please let me know.

Liters	Date	Person	km driven
33	18-3-2022	A	55800
36	2-4-2022	A	56204
25	15-4-2022	A	56670
30	4-5-2022	A	57075
24	14-5-2022	A	57515
30	26-5-2022	A	57962
28	8-6-2022	A	58483
20	25-6-2022	A	58914
40	19-3-2022	B	35000
34	3-4-2022	B	35523
33	23-4-2022	B	36050
29	4-5-2022	B	36484
21	21-5-2022	B	36960
35	4-6-2022	B	37445
22	23-6-2022	B	37980
27	5-7-2022	B	38486
35	20-3-2022	C	28000
33	4-4-2022	C	28545
22	16-4-2022	C	28969
31	26-4-2022	C	29426
23	12-5-2022	C	29881
20	28-5-2022	C	30385
21	7-6-2022	C	30926
21	27-6-2022	C	31358
37	5-4-2022	D	33560
26	15-4-2022	D	34002
28	25-4-2022	D	34462
32	15-5-2022	D	34868
27	4-6-2022	D	35292
30	16-6-2022	D	35789
35	4-7-2022	D	36200
28	6-4-2022	E	34500
25	24-4-2022	E	34966
20	14-5-2022	E	35463
25	28-5-2022	E	35876
28	15-6-2022	E	36288
20	28-6-2022	E	36741
21	10-7-2022	E	37203

[v-rzhou-msft · Accepted solution]

Hi @WTsrnl ,

I think you can try this code to calculate total liters exculde first date per person.

Total Exclude First Date =
VAR _SUMMARIZE =
    SUMMARIZE (
        'Table',
        'Table'[Person],
        "Liters exclude first date",
            VAR _First_Date =
                CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Person] ) )
            RETURN
                CALCULATE (
                    SUM ( 'Table'[Liters] ),
                    FILTER ( ALLEXCEPT ( 'Table', 'Table'[Person] ), 'Table'[Date] > _First_Date )
                )
    )
RETURN
    SUMX ( _SUMMARIZE, [Liters exclude first date] )

Result is as below.

Best Regards,
Rico Zhou

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

[WTsrnl]

Works! Thanks, the summarize function was unknown to me, but very handy to group certain tables within a measure.