Forum Discussion

georgec96's avatar
georgec96
Icon for Helper II rankHelper II
4 years ago
Solved

Previous Row on Grouped data

Hi guys, 

 

I'm having trouble writing a DAX formula that would give me the previous row for a dataset.

 

Basically I have a table with the following columns : date, user, type, category, and time of transaction. I need to group my data by user, date, type category and then get the previous time of transaction for each user.

 

I believe this would be similiar to a PARTITION BY in SQL however I'm not sure how to achieve this in DAX.

 

Any help would be greatly appreciated.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi georgec96 ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a column.

    Previous transaction date = 
var currentDate =A[Transaction date]
var currentCustomer =A[client]
var currentProduct = A[product_name]
return
CALCULATE(MAX(A[Transaction date]),
    FILTER(ALL(A),
        A[Transaction date] < currentDate
        && A[client] = currentCustomer
	&& A[product_name] = currentProduct
    )
)

    If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago
    Previuos Time Per User =
    VAR _current_time = MAX(PreviousTime[time])
    VAR _table =
    CALCULATETABLE(
    FILTER(
    PreviousTime,
    PreviousTime[time] < _current_time
    ),
    REMOVEFILTERS(PreviousTime),
    VALUES(PreviousTime[Date]),
    VALUES(PreviousTime[user])
    )
    VAR _result =
    MAXX(_table, PreviousTime[time])
    RETURN
    _result

    This version will return blank in case there weren't any rows with time before (as in the first row in that day for that user). If you want this to show the previous time also when it was in the previous date than best to add another column with date and time and do the calculation of the max and previous on it. In this case you need to remove the VALUES(PreviousTime[Date]) from the measure I wrote
  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago
    Previuos Time Per User =
    VAR _current_time = MAX(PreviousTime[time])
    VAR _table =
    CALCULATETABLE(
    FILTER(
    PreviousTime,
    PreviousTime[time] < _current_time
    ),
    REMOVEFILTERS(PreviousTime),
    VALUES(PreviousTime[Date]),
    VALUES(PreviousTime[user])
    )
    VAR _result =
    MAXX(_table, PreviousTime[time])
    RETURN
    _result

    This version will return blank in case there weren't any rows with time before (as in the first row in that day for that user). If you want this to show the previous time also when it was in the previous date than best to add another column with date and time and do the calculation of the max and previous on it. In this case you need to remove the VALUES(PreviousTime[Date]) from the measure I wrote
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi georgec96 ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a column.

    Previous transaction date = 
var currentDate =A[Transaction date]
var currentCustomer =A[client]
var currentProduct = A[product_name]
return
CALCULATE(MAX(A[Transaction date]),
    FILTER(ALL(A),
        A[Transaction date] < currentDate
        && A[client] = currentCustomer
	&& A[product_name] = currentProduct
    )
)

    If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • georgec96's avatar
      georgec96
      Icon for Helper II rankHelper II
      3 years ago

      Hi Anonymous , my date column is a date/time and now the data set increased to few million rows this seems not to work anymore, keeps spinning forever is there any way to get around this?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Hi georgec96 
        I did a small modification on Anonymous formula. Please try

        Previous transaction date =
VAR CurrentDate = A[Transaction date]
VAR T1 =
    CALCULATETABLE ( A, ALLEXCEPT ( A, A[client], A[product_name] ) )
VAR T2 =
    FILTER ( T1, A[Transaction date] < CurrentDate )
RETURN
    MAXX ( T2, A[Transaction date] )