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/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.

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
    )

3 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    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
    )
    • newbie7's avatar
      newbie7
      Frequent Visitor
      5 years ago

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

  • 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.