sum of sales on the max available date of each month

[Mehtab]

I am trying to find out the sum of sales on the max available date of each month.

i am using the below set analysis:

SUM({<[Date]= {"=Aggr(max([Date]),[Month])"}>}[Sales])

the above expression returns the sum of sales on the first day of each month

even though Aggr(max([Date]),[Month]) returns the correct max available date of each month.

Please help

[pradosh_thakur]

If you have month as a dimension the below shall work

SUM({<[Date]= {"$(=max([Date]))"}>}[Sales])

[Mehtab]

It gives the sum sales on the max date of the max month.

However i need the sum of sales on the max date of all the month.

I need to make a master measure like that .

Hence when put against any dimension it should give the sum of sales on the max date of each month for that dimension

[pradosh_thakur]

as i said if you have month as a dimension the above expression shall work.

[Mehtab]

It is not. It is only giving the sum of sales for the latest month.

[ferryjong]

Hi,

Maybe using the Dollar Sign Expansion in your Set Analysis will help, like:

SUM({<[Date]= {"$(=Aggr(max([Date]),[Month]))"}>}[Sales])

[pradosh_thakur]

My bad. I didn't realise what you wanted. Set analysis is calculated once per chart so taht's obvious.

your expression shall work, i tried it.

What dimension are you using? I tried with similar sample data and it's working for me.

[Mehtab]

I get no data using dollar sign in this expression

[Mehtab]

I am using month as the dimension

[Mehtab]

when i am using the below expression
FirstSortedValue(Aggr(sum([Sales]), [Date]), -[Date])
i am getting correct output but only for Month dimension when any other dimension is being used it gives wrong values