Forum Discussion

newbie7's avatar
newbie7
Frequent Visitor
5 years ago
Solved

Data shaping issue

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/...
Need Help
  • PaulOlding's avatar
    PaulOlding
    5 years ago

    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
    )
rbriga's avatar
rbriga
Impactful Individual
5 years ago

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.