Forum Discussion
Aggregating latest value only for multiple IDs
Hi folks,
Been struggling with this one for a while now. I am very close but could use a little help to finish this one up. I am simply looking to take the last value of a duplicate ID in a time period and aggregate the results. I am doing this with the measures at the end of this explanation of my challenge here. Thanks you very much for taking the time to look at this any help woudl be very much appreciated.. 🙂
I have a data set that looks like this:
ValidFromDate | Parent ID | Amount_hst | Probability_hst
--------------------------------------------------------------
4/8/2022 5:00 | 1 | 1 | 10
4/8/2022 5:30 | 1 | 1 | 20
5/8/2022 6:00 | 2 | 1 | 20
5/8/2022 6:15 | 3 | 1 | 20
6/8/2022 6:01 | 4 | 1 | 20
6/8/2022 6:05 | 5 | 1 | 20
This is the result I am trying to get and I mostly get this result but there is one important subdelty
Table results
Date | Total Amount
------------------------------------
4/8/2022 | 1
5/8/2022 | 2
6/8/2022 | 2
Card result = 5
the problem comes in when I try to build a stacked bar chart with the probability column as a legend. The bar graph displays both entries and stacks the results showing a 1 and a 1 instead of just a single 1 on the line.
I only want the stacked bar chart to show the line with a probability of 20 because it was the latest entry in the month of April.
ValidFromDate | Parent ID | Amount | Probability
--------------------------------------------------------------
4/8/2022 5:00 | 1 | 1 | 10
4/8/2022 5:30 | 1 | 1 | 20
First Measure
var _maxdate = CALCULATE(MAX('Table'[ValidFromDate]), VALUES('Table'[ParentId]))
Second Measure which uses the first measure
var _table = SUMMARIZE('Opportunity (Historical)','Opportunity (Historical)'[ParentId], "_value", [Max Date])
Thank you so much for the help and happy to share more info if helpful.
Hi, JL011 ;
You could try it.
Total Amount1 = var _maxdate = CALCULATE(MAX('Table'[ValidFromDate]),FILTER(ALL('Table'),EOMONTH([ValidFromDate],0)=EOMONTH(MAX('Table'[ValidFromDate]),0))) return IF(MAX('Table'[ValidFromDate])=_maxdate, CALCULATE(SUM([Probability_hst]),FILTER(ALL('Table'),[ValidFromDate]=_maxdate)))Max Opportunity By Last Date = var _table = SUMMARIZE('Table',[ParentId], "_value", [Total Amount1]) return SUMX( _table, [_value])The final show：
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
JL011 , Refer if my blog on the same topic, how to correct that can help
or the video https://www.youtube.com/watch?v=7MRksDy0rWg
amitchandak
Thank you for your response. I will take a look when I get to the office later today. I was able to find a solution that works for me late last night by creating a "month number" column and concatonating that column with the "parent ID" column. That seems to work for my solution but it is not very elegant and or portable to other reports that I am running.I will let you know if your solution is an alternate way to solve the challenge I have.
Thannks!
Hi, JL011 ;
You could try it.
Total Amount1 = var _maxdate = CALCULATE(MAX('Table'[ValidFromDate]),FILTER(ALL('Table'),EOMONTH([ValidFromDate],0)=EOMONTH(MAX('Table'[ValidFromDate]),0))) return IF(MAX('Table'[ValidFromDate])=_maxdate, CALCULATE(SUM([Probability_hst]),FILTER(ALL('Table'),[ValidFromDate]=_maxdate)))Max Opportunity By Last Date = var _table = SUMMARIZE('Table',[ParentId], "_value", [Total Amount1]) return SUMX( _table, [_value])The final show：
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
