Sum latest values based on date (duplicate dates)

[oitp]

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

[oitp]

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

@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

[az38]

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]

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]

@oitp 

maybe you've got the other fields?

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

[oitp]

I have updated the measure to match my fields and tables. I think there is something wrong with my tables and relationships. The table I have explained is actually a table based on relationships. I have to look into that I believe 🙂

[az38]

@oitp you can show us data example and data model then we'll try to fix it together

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

[oitp]

How do I do that? Can I share something for you or do you want some print screens? 🙂

[az38]

@oitp 

yes, screenshots. ot ypu could upload ypur pbix-file to the cloud like https://uploadfiles.io/

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

[oitp]

Thanks @az38 ! I sent you the pbix-file through PM now 🙂

[az38]

Hi @oitp 

ive reached the limit for number of private messages 🙂

try:

1. add measure to the left table visual

2. create and show result calculation a measure 

calculate(max(Table1[Updated Date]);all(Table1))

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

[oitp]

Hi, I am not sure about what you mean with this stage? Should I create a new measure for this and then just show it through a table or so?

2. create and show result calculation a measure 

calculate(max(Table1[Updated Date]);all(Table1))