Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
oitp
Helper I
Helper I

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

 

12 REPLIES 12
oitp
Helper I
Helper I

@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
Community Champion
Community Champion

@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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

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


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

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
Community Champion
Community Champion

@oitp 

maybe you've got the other fields?

877540.PNG

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


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

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
Community Champion
Community Champion

@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


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

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

az38
Community Champion
Community Champion

@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


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

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

az38
Community Champion
Community Champion

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


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

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))

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.