Forum Discussion

zivhimmel's avatar
zivhimmel
Icon for Resolver I rankResolver I
9 years ago
Solved

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:

 

 

 

 

 

 

 

 

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 !

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

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

     

    Regards

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. :smileyhappy:

    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's avatar
      zivhimmel
      Icon for Resolver I rankResolver I
      9 years ago

      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.