SUM w/o duplicates for dynamic list of dates

[yvesdeutschmann]

Hi,

I'm trying to accomplish a seemingly easy aggregation but just can't wrap my head around how exactly. I have 3 tables:

• Prices: PartID, Price, GroupID, Date
  • PartID can occur multiple times
• DimDate: all Days for a certain interval
• Mondays: Dates of all Mondays for a certain interval
  • The list of Mondays is generated dynamically and will grow over time

What I'm ultimately looking for is the sum of all prices before each Monday in the above list.

• if a PartID is duplicated in the list, I only want the most recent entry to be considered for the sum
• I want to be able to group the result be GroupID

The chart I want to build looks something like this, only with the Mondays on the x-axis.

Can someone point me in the right direction on how to do this? This is the PBIX Sample.

TIA Yves

[yvesdeutschmann · Accepted solution]

I finally figured it out. In case anyone runs into the same problem, here's what I did:

I had to remove the connection between Prices[Date] and DimDate[Date]:

After that the measures, I had in place finally worked together:

• SelectedDate = SELECTEDVALUE(DimDate[Date]) => represents the "selected" Monday

• LatestPrice =

VAR _maxdate = [SelectedDate]

RETURN CALCULATE( SELECTEDVALUE(Prices[Price]),  Prices[Date] < _maxdate  )

• TotalProductCost =
  VAR _table = SUMMARIZE(  Prices, Prices[PartID], "_value", [LatestPrice])
  RETURN SUMX(_table, [_value])
  

The updated PBIX can be found HERE and now looks like this:

Regards,

Yves