Set Analysis to find last possible day in week

[pauldamen]

All,

I have a graph where I show product balances per week. In a week there can be multiple transaction but I am only interested in the last balance that was entered. This can be on Wednesday but also for example on Friday.

I have written the following formula: Sum(aggr(Max({<WeekDay = {$(=Max(WeekDay))}>} Balance), WeekYear, ProductID, LocationID)

This works, other then the fact that it fixes my WeekDay (this is a number 1-7 for Monday-Sunday). The problem is that is fixes it to 6 for example but not every week has a 6. What I need is that this day number needs to be changed per week to find the last day in that week and show the corresponding balance.

Hope you guys can help.

Best regards,

Paul

[Anil_Babu_Samineni]

I can think to generate the key using Autonumber() and use that field into measure

[sunny_talwar]

How about this?

Sum(Aggr(
If(WeekDay = Max(TOTAL <WeekYear, ProductID, LocationID> WeekDay), Sum(Balance))
, WeekDay, WeekYear, ProductID, LocationID)