Calculate SUM for latest dates

[zivhimmel]

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 !

[v-ljerr-msft · Accepted 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

[zivhimmel]

@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]

Anyone?