The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone,
I am comparing statistics from Messi and Ronaldo. I have column with Date, Player and Goals. And I created a cumulative measure, which is as follows:
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, here's my solution.
1.Create a new table.
Table =
GENERATE (
VALUES ( Stats[Date] ),
UNION ( ROW ( "Player", "Messi" ), ROW ( "Player", "Ronaldo" ) )
)
2.Create a calculated column in the new table.
Goal =
IF (
MAXX (
FILTER (
ALL ( 'Stats' ),
'Stats'[Date] = EARLIER ( 'Table'[Date] )
&& 'Stats'[Player] = EARLIER ( 'Table'[Player] )
),
'Stats'[Goals]
)
<> BLANK (),
MAXX (
FILTER (
ALL ( 'Stats' ),
'Stats'[Date] = EARLIER ( 'Table'[Date] )
&& 'Stats'[Player] = EARLIER ( 'Table'[Player] )
),
'Stats'[Goals]
),
0
)
3.Create a measure.
Cumulative goals =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Player] = MAX ( 'Table'[Player] )
),
'Table'[Goal]
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, here's my solution.
1.Create a new table.
Table =
GENERATE (
VALUES ( Stats[Date] ),
UNION ( ROW ( "Player", "Messi" ), ROW ( "Player", "Ronaldo" ) )
)
2.Create a calculated column in the new table.
Goal =
IF (
MAXX (
FILTER (
ALL ( 'Stats' ),
'Stats'[Date] = EARLIER ( 'Table'[Date] )
&& 'Stats'[Player] = EARLIER ( 'Table'[Player] )
),
'Stats'[Goals]
)
<> BLANK (),
MAXX (
FILTER (
ALL ( 'Stats' ),
'Stats'[Date] = EARLIER ( 'Table'[Date] )
&& 'Stats'[Player] = EARLIER ( 'Table'[Player] )
),
'Stats'[Goals]
),
0
)
3.Create a measure.
Cumulative goals =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Player] = MAX ( 'Table'[Player] )
),
'Table'[Goal]
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@amitchandak I don't, but I can create a date table. It will be a one-to-one relationship between the date column in my Stats table to the Date[date]. So, I am not sure if that work, but the Date table isn't linked to a specific player, if you're aiming for that.
@Anonymous , Try with +0
Cumulative goals =
CALCULATE (
SUM ( 'Stats'[Goals] ),
FILTER (
ALL ( 'Stats' ),
'Stats'[Date] <= MAX ( 'Stats'[Date] )
&& 'Stats'[Player] = MAX ( 'Stats'[Player] )
))+0
@amitchandak Thank you, and that would work, but in this case, it doesn't.
I think it's because there's only one day per player:
When Ronaldo scores and Messi doesn't, you have a row with the specific date, the number of goals and Ronaldo.
@Anonymous , do you have a Separate date and player table ? In that case this might work
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |