Help using first sorted value

[ali_hijazi]

Hello got a fact table with  customers and date of transaction

I need to create a pivot table with dimension month_year and customer key

What I want to display is the max date for each customer before the month year  value in the dimension

[sunny_talwar]

Max date? or value associated with max date?

For Max date: Max(DateField)

For value associated with max date: FirstSortedValue(Value, -Date)

[vishsaggi]

May be this: Change the fields accordingly.

= FirstSortedValue(Datefield, -Aggr(Max(Date(DateField, 'DD-MM-YYYY'), Customer))

[ali_hijazi]

Where in this expression are you specifying to get the dates prior to date value of dimension?

[neelamsaroha157]

You can also create a table in script with the Month_Year and customer as key and Min of Date field aggregated by the key.

This way you will get Min of Date as 1 record per Year_Month_Customer combination.

[swuehl]

"What I want to display is the max date for each customer before the month year  value in the dimension"

Not sure I understand what you want to achieve.

Do you want to display a value in the dimension section of your table, left of the month year dimension?

Or do you want to create an expression that calculated the max(Date) before the month year per customer (so, for the previous month with regard to the current year month dimension value)?

Maybe some sample data and description of your expected result may help to understand.

[ali_hijazi]

Check the sample document I supplied in the following thread

Help in expression FOR LOST CUSTOMERS

Yes I want for each month year and customer on pivot table to get the last time that customer had a sales transaction I.e if I select June 2016 customer c1

Then next to June 2016 c1 display for example June 2015 if June 2015 was the last time this customer was invoiced before June 2016