Forum Discussion

liamr0639's avatar
liamr0639
New Member
2 years ago

Return SUM for newest value by unique ID

Hi all,

 

I'm pretty new to PowerBi but I have got good familarity.

 

I want to return a SUM value (so I can put in a card visual) of a SUM of values only by unique IDs and their latest date of values.

 

This means I can put a date slider which filters the latest SUM values (by unique IDs) in a date range.

 

Example data:

 

IDAmountDateTime
11001/01/2023
1502/05/2023
1201/08/2023
21501/01/2023
21002/05/2023
2501/08/2023



I want to then add a filter slider and if I select January and it only shows the SUM value of 25. But if I change the slider for May it will show me the SUM value of 15. If I set the date slider for the whole of 2023 I want it to only show the latest data which is a SUM of 7.

 

I can create DAX to SUM latest values only but if I change the slider to January there is no data. So I need to be able to create a SUM based on unique IDs only and their latest date based on a filter slider.

 

Many thanks,

 

Liam

"DAX" "Earliest date"

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi,

    Here is one way to do this:

    Latest or sum =
    var _latest = CALCULATE(Month(MAX('Table (11)'[DateTime])),ALL('Table (11)')) return

    IF(HASONEFILTER('Calendar'[Month]),SUM('Table (11)'[Amount]),CALCULATE(SUM('Table (11)'[Amount]),ALL('Table (11)'[DateTime]),MONTH('Table (11)'[DateTime])=_latest))

    End result:

     

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • liamr0639's avatar
    liamr0639
    New Member
    2 years ago

    Hi, many thanks for this!

     

    Do you know if it's possible to do by day also? As I want the slider to be in days between a period.

     

    Many thanks

    • ValtteriN's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Sure, something like this might work depending on the filters you use:

      Latest or sum =
      var _latest = CALCULATE(Month(MAX('Table (11)'[DateTime])),ALL('Table (11)'))
      var _latestS = MAX('Calendar'[Month])
       return
      IF(COUNTROWS(FILTER(ALL('Table (11)'),MONTH('Table (11)'[DateTime])=_latestS))=0,
      CALCULATE(SUM('Table (11)'[Amount]),ALL('Table (11)'[DateTime]),MONTH('Table (11)'[DateTime]))=_latest,SUM('Table (11)'[Amount]))