DAX - Problem to identify previous record

[karlo]

Hi everyone,

I'm starting with DAX and I need your help, for a particular record I am trying to identify the previous record if the start date of the previous record is less. Also, if there is more than one record, sort them alphabetically and include them as groups.

For example, considering the following matrix:

ID	CONTRACT	START	PRODUCT
1	1	01-01-2016	A
2	3	01-01-2016	C
1	4	01-01-2019	B
2	5	01-01-2017	A
1	6	01-01-2018	C

The result with PRIVIOUS is:

ID	CONTRACT	START	PRODUCT	PREVIOUS
1	1	01-01-2016	A
2	3	01-01-2016	C
1	4	01-01-2019	B	A-C
2	5	01-01-2017	A	C
1	6	01-01-2018	C	A

I would greatly appreciate your help or comments, I have done several tests without good results. In some cases I have memory problems (I have 8GB RAM).

Thank you

Karlo

[Anonymous · Accepted solution]

@karlo 
Please create a measure 

Measure = CALCULATE(CONCATENATEX('Table','Table'[PRODUCT],"-",'Table'[PRODUCT]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[START]<MAX('Table'[START])))

 

[karlo]

Excellent vimal_parmar, I'll try again

Thank you very much!