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,
Here's what I'm trying to do, complete with data samples.
Let's assume I have a "contributions" table, which looks like this:
As you can see, a user can contribuate at various times. Pretty basic.
I also have a date dimension:
The two tables are connected, using DateID.
I'm trying to display a number showing the sum of the latest contributions.
So, if not filtering at all, the value should be:
150(the latest contribution of user 1)+85(the latest contribution of user 2)+ 77(the latest contribution of user 3) = 312
If filtering, for example for the perion of 1-15/6, the value should be:
150(the latest contribution of user 1)+85(the latest contribution of user 2)=235.
Can anyone suggest a DAX formula for such calculated measure? I've seen examples of calcualted tables and columns,
but I'm afraid that won't work, because I need to have flexibility based on filters selected.
If not, my next step is to do some work in the database first - make sure each user has a record on each date with their latest contribution, and then use a DAX formula summarizing contributions for the latest date.
Thanks !
Solved! Go to Solution.
Hi @zivhimmel,
Based on my test, you should be able to use the formula below to create a new measure to calculate the sum of the latest contributions in your scenario.
sum of the latest contributions = SUMX ( SUMMARIZE ( contributions, contributions[User], "LatestContributions", CALCULATE ( SUM ( contributions[Contribution] ), FILTER ( contributions, contributions[DateID] = MAX ( contributions[DateID] ) ) ) ), [LatestContributions] )
Regards
Hi @zivhimmel,
Based on my test, you should be able to use the formula below to create a new measure to calculate the sum of the latest contributions in your scenario.
sum of the latest contributions = SUMX ( SUMMARIZE ( contributions, contributions[User], "LatestContributions", CALCULATE ( SUM ( contributions[Contribution] ), FILTER ( contributions, contributions[DateID] = MAX ( contributions[DateID] ) ) ) ), [LatestContributions] )
Regards
@v-ljerr-msft, this seems perfect.
As you can imagine, my real model is a bit reacher, so I'll try it tomorrow with real data and make sure it's working.
Thanks allot!
Update the next day: working very well. Thanks again.
Anyone?
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |