Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 sale | customer | product | sales status | date of purchase | previous date of purchase | previous sales status |
Dec-16 | john | A | booked | 01-12-16 | null | null |
Dec-16 | john | A | booked | 20-12-16 | 01-12-16 | booked |
Dec-16 | alicia | A | paid | 01-12-16 | null | null |
Jan-17 | john | Ck111 | booked | 01-01-17 | null | null |
Jan-17 | john | Ck111 | paid | 13-01-17 | 01-01-17 | booked |
Jan-17 | john | A | booked | 01-01-17 | null | null |
Jan-17 | john | A | booked | 03-01-17 | 01-01-17 | booked |
Jan-17 | alicia | Ck111 | booked | 01-01-17 | null | null |
Jan-17 | alicia | Ck111 | paid | 13-01-17 | 01-01-17 | booked |
Jan-17 | alicia | Ck111 | paid | 20-01-17 | 13-01-17 | paid |
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
Solved! Go to Solution.
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] )
Best Regards,
Qiuyun Yu
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] )
Best Regards,
Qiuyun Yu
https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298
help me out on the same issue.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
54 | |
54 | |
37 | |
29 |
User | Count |
---|---|
78 | |
64 | |
45 | |
40 | |
40 |