Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Works! Thanks, the summarize function was unknown to me, but very handy to group certain tables within a measure.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |