Forum Discussion

JL011's avatar
JL011
Regular Visitor
4 years ago
Solved

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...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

JL011's avatar
JL011
Regular Visitor
4 years ago

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!