cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

add previous row values

Hi,

 

I have a fact table of sales and I need to get 2 new columns, wich will display Previous date of purchase and Previous sales status (both red in the table below).

 

I'm trying to get same results as with using lag/window function in SQL ( lag(date_of_purchase) over (partition by month_of_sale, customer, product order by date_of_purchase). 

Also, the problem is, I no longer can edit Query (the table in database no longer exists)..

 

month of salecustomerproductsales statusdate of purchaseprevious date of purchaseprevious sales status
Dec-16johnAbooked01-12-16nullnull
Dec-16johnAbooked20-12-1601-12-16booked
Dec-16aliciaApaid01-12-16nullnull
Jan-17johnCk111booked01-01-17nullnull
Jan-17johnCk111paid13-01-1701-01-17booked
Jan-17johnAbooked01-01-17nullnull
Jan-17johnAbooked03-01-1701-01-17booked
Jan-17aliciaCk111booked01-01-17nullnull
Jan-17aliciaCk111paid13-01-1701-01-17booked
Jan-17aliciaCk111paid20-01-1713-01-17paid

 

 

I've been trying to use calculate function with filters, but so far no useful results. I'm new at DAX, so probably I'm not using them right way..

 

Thanks in advance for any help!

Zuzana

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

You can create calculated columns follow below DAX:

 

previous date of purchase = CALCULATE(MAX('Table1'[date of purchase]),FILTER(Table1,'Table1'[month of sale]=EARLIER(Table1[month of sale]) && 'Table1'[customer]=EARLIER(Table1[customer])&&'Table1'[product]=EARLIER(Table1[product])&&'Table1'[date of purchase]<EARLIER('Table1'[date of purchase])))

 

previous sales status = LOOKUPVALUE( Table1[sales status], Table1[month of sale], Table1[month of sale], Table1[customer], Table1[customer], Table1[product], Table1[product], Table1[date of purchase], Table1[previous date of purchase] )

 

q3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

You can create calculated columns follow below DAX:

 

previous date of purchase = CALCULATE(MAX('Table1'[date of purchase]),FILTER(Table1,'Table1'[month of sale]=EARLIER(Table1[month of sale]) && 'Table1'[customer]=EARLIER(Table1[customer])&&'Table1'[product]=EARLIER(Table1[product])&&'Table1'[date of purchase]<EARLIER('Table1'[date of purchase])))

 

previous sales status = LOOKUPVALUE( Table1[sales status], Table1[month of sale], Table1[month of sale], Table1[customer], Table1[customer], Table1[product], Table1[product], Table1[date of purchase], Table1[previous date of purchase] )

 

q3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Is there a way to do this with a measure instead of a column? My table has too many rows and there is not enough memory to solve it with a calculated column.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors