Refer an aggregated date in set expression or sumif

[hytown2000]

Hi gurus,

I have a challenge to use an aggregated date Max(Date) in another formula to calculate Sales Amount for that aggregated date.

Last Sold Date is defined as Max(Date)

My-Sales LSD is defined as Sum({<Date={'$(=max(Date))'},Manufacturer={'Me'}>}Amount)

Last Sold Date is working fine, but My-Sales LSD is wrong, it takes the Max(Date) for the whole set, instead of for each Material, I understand why it happens, as expression in set analysis evaluates the whole chart. I tried Sum(If...) and it is the same.

[sunny_talwar]

Try this

FirstSortedValue({<Manufacturer={'Me'}>}Amount, -Date)

[sunny_talwar]

Or this

FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Date), -Date)

[hytown2000]

This is a good idea, but it does not work if I added one more sale line,

M1, C1, Me, 4/1/2017, 10

[sunny_talwar · Accepted solution]

Try this

FirstSortedValue({<Manufacturer={'Me'}>}Aggr(Sum({<Manufacturer={'Me'}>}Amount), Material, Customer, Manufacturer, Date), -Aggr(Date, Material, Customer, Manufacturer, Date))

[hytown2000]

This works like a charm, later I found it returns null if there are multiple customers on the same date, but I managed to solve it using your idea with the below formula.

FirstSortedValue(Aggr(Sum({<Manufacturer={'Me'}>}Amount),Date,Material),-Aggr(Date,Date,Material))