Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
zivhimmel
Resolver I
Resolver I

Calculate SUM for latest dates

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:

data1.PNG

 

 

 

 

 

 

 

 

As you can see, a user can contribuate at various times. Pretty basic.

I also have a date dimension:

data2.PNG

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 !

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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. Smiley Happy

sum of the latest contributions =
SUMX (
    SUMMARIZE (
        contributions,
        contributions[User],
        "LatestContributions", CALCULATE (
            SUM ( contributions[Contribution] ),
            FILTER ( contributions, contributions[DateID] = MAX ( contributions[DateID] ) )
        )
    ),
    [LatestContributions]
)

r1.PNG

 

Regards

View solution in original post

3 REPLIES 3
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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. Smiley Happy

sum of the latest contributions =
SUMX (
    SUMMARIZE (
        contributions,
        contributions[User],
        "LatestContributions", CALCULATE (
            SUM ( contributions[Contribution] ),
            FILTER ( contributions, contributions[DateID] = MAX ( contributions[DateID] ) )
        )
    ),
    [LatestContributions]
)

r1.PNG

 

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.

zivhimmel
Resolver I
Resolver I

Anyone?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.