Data shaping issue

[newbie7]

hello experts,

Need some help what i have been trying to resolve forwhole day, but could not. 

column1                  Date1           Date2

Customer1   03/12/2010    06/09/2013

Custermer1   06/09/2018    05/03/2010

Customer1   03/12/2010    06/06/2012

Customer2  09/11/2017     07/06/2017

Cstomer2   05/11/2018       09/25/2018.......

Output i am looking for

column1       date1         date2

customer1  03/12/2010   05/03/2010

customer1  06/09/2018    BLNK

Customer1   03/12/2010    05/03/2010

Customer2   09/11/2017   09/25/2018

Customer2  05/11/2018   09/25/2018      

so basically, for each customer,with each row in Date1 field, it should look for the nearest after date from date2 field for respective customer, if none found, then return blank.

dax/M: anything to make it work?  Thanks for the hep in advance.

[rbriga]

I think you'd first need to flatten the data- either in SQL (always better to perform this at the source) or in M.

A. Create two tables:

1) Customer abd date (from Date 1)

column1                  Date

Customer1   03/12/2010

Custermer1  06/09/2018

...

2) Customer abd date (from Date 1)

column1                  Date

Customer1    06/09/2013

Custermer1  05/03/2010

...

B. Union them:

column1                  Date

Customer1   03/12/2010

Custermer1  06/09/2018

Customer1    06/09/2013

Custermer1  05/03/2010

C. Remove duplicates and sort by customer and date.

It would help to add a row number column.

D. Create a customer-date 1- date 2 table.

In SQL, you can do that by joining the table into itself:

From <Table> T1

LEFT JOIN <Table> T2

ON

T1.[rownumber] = T2.[rownumber]+1 AND T1.[column1]=T2.[column1]

That should give you the desired resault.

If you're not using SQL, this might help to get the previous row.

I hope this helps.

[PaulOlding · Accepted solution]

Here's some DAX for a calculated column

Table[NewDate2] =

VAR _CurrentDate1 = 'Table'[Date1]

RETURN

CALCULATE(

MIN('Table'[Date2]),

ALLEXCEPT('Table', 'Table'[Column1]),

'Table'[Date2] > _CurrentDate1

)

[newbie7]

dax is working, i just added >= as there were a few same dates. Thanks much