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

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