Forum Discussion

oitp's avatar
oitp
Helper I
6 years ago

Sum latest values based on date (duplicate dates)

Hi, I have a table as the table describes. I want to sum the values that has the latest UpdatedDate. So if we keep the table as an example I want to sum the three rows with the UpdatedDate 2019-12-15 01:05:36. 


How can I do this? When I try the measure below I only get an error saying that "A date column containing multiple dates was specified in the call to function 'LASTDATE'. This is not supported"

 

Measure: CALCULATE(SUM(Counter);LASTDATE(UpdatedDate)

 

Updated Date

Counter

2019-12-15 01:05:36

46540

2019-12-15 01:05:36

5249

2019-12-15 01:05:36

51789

2019-11-25 11:23:09

44517

2019-11-25 11:23:09

4980

2019-11-25 11:23:09

49497

 

Need Help

12 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    hi oitp 

    try a measure

    Measure = calculate(Sum(Table1[Counter]);filter(all(Table1);Table1[Updated Date]=calculate(max(Table1[Updated Date]);all(Table1))))

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • oitp's avatar
      oitp
      Helper I
      6 years ago

      Thanks az38, but this will get me a blank result. I do not get any errors but the measure is empty. Can it be something wrong with the format of the Counter column?

      • az38's avatar
        az38
        Community Champion
        6 years ago

        oitp 

        maybe you've got the other fields?

        do not hesitate to give a kudo to useful posts and mark solutions as solution

  • oitp's avatar
    oitp
    Helper I
    6 years ago

    az38 it seems like the measure works in import mode. If you have any ideas on how to get it working in direct query I would be very happy, otherwise I will go ahead with import! 🙂

    • az38's avatar
      az38
      Community Champion
      6 years ago

      oitp 

      Measure2 = 
var maxdate = calculate(max(Table1[Updated Date]))
return
calculate(Sum(Table1[Counter]);all(Table1);Table1[Updated Date]=maxdate)

      do not hesitate to give a kudo to useful posts and mark solutions as solution