Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi I have the following table that contains orders and transactions within several days. Please note that not all orders become transactions and that transaction time does not always follow order time (latest order may be served before newest).
Order Date | Order Time | Order Price | Transaction Time | Transaction Value |
1/1/2021 | 13:30:12 | 0.5 | 14:20:55 | 0.38 |
1/1/2021 | 15:20:10 | 0.4 | 16:15:08 | 0.39 |
2/1/2021 | 08:12:02 | 0.38 | ||
2/1/2021 | 09:15:43 | 0.36 | 11:20:43 | 0.36 |
2/1/2021 | 10:13:45 | 0.35 | 13:55:35 | 0.39 |
2/1/2021 | 11:17:06 | 0.33 | 12:00:45 | |
5/1/2021 | 20:23:20 | 0.38 | 20:23:25 |
So, I'm trying to create an extra column (or measure) that holds, for every order, the previous price. Previous price is the price of the latest transaction, at the time of the order. If there haven't been any transactions during current day, Previous Price is the value of the latest transaction of previous day. Please note that there are days with no transactions at all, so we may need to look several days before.
The expected result would look like
Order Date | Order Time | Order Price | Transaction Time | Transaction Value | Previous Price | Explanation |
1/1/2021 | 13:30:12 | 0.5 | 14:20:55 | 0.38 | Start -> no transactions yet | |
1/1/2021 | 15:20:10 | 0.4 | 16:15:08 | 0.39 | 0.38 | latest transaction on 15:20:10 |
2/1/2021 | 08:12:02 | 0.38 | 0.39 | latest transaction of the previous day | ||
2/1/2021 | 09:15:43 | 0.36 | 11:20:43 | 0.36 | 0.39 | On 09:15:43 no transactions yet, get latest of previous day |
2/1/2021 | 10:13:45 | 0.35 | 13:55:35 | 0.39 | 0.39 | On 10:13:45 no transactions yet, get latest of previous day |
2/1/2021 | 11:47:06 | 0.33 | 12:00:45 | 0.36 | On 11:47:06 get price of previous transaction (effected on 11:20:43) | |
5/1/2021 | 20:23:20 | 0.38 | 20:23:25 | 0.39 | No transactions for days get the latest transaction of 2/1/2021 (13:55:35) |
I' ve tried to use lastnonblank, filter with earlier but with no success so far.
I would greatly appreciate any help.
Thanks in advance!
Solved! Go to Solution.
@Anonymous
you can create three colums
Orderdatetime = 'Table'[Order Date]+'Table'[Order Time]
transactiondatetime =
VAR _date=maxx(FILTER('Table','Table'[Order Date]<EARLIER('Table'[Order Date])),'Table'[Order Date])
return if(ISBLANK('Table'[Transaction Time]),_date+maxx(FILTER('Table','Table'[Order Date]=_date),'Table'[Transaction Time]),'Table'[Order Date]+'Table'[Transaction Time])
previousprice =
VAR _time='Table'[Orderdatetime]
VAR _previous= MAXX(FILTER(all('Table'),'Table'[transactiondatetime]<=_time&¬(ISBLANK('Table'[Transaction Value ]))),'Table'[transactiondatetime])
return maxx(FILTER('Table','Table'[transactiondatetime]=_previous),'Table'[Transaction Value ])
please see the attachment below
Proud to be a Super User!
@Anonymous
you can create three colums
Orderdatetime = 'Table'[Order Date]+'Table'[Order Time]
transactiondatetime =
VAR _date=maxx(FILTER('Table','Table'[Order Date]<EARLIER('Table'[Order Date])),'Table'[Order Date])
return if(ISBLANK('Table'[Transaction Time]),_date+maxx(FILTER('Table','Table'[Order Date]=_date),'Table'[Transaction Time]),'Table'[Order Date]+'Table'[Transaction Time])
previousprice =
VAR _time='Table'[Orderdatetime]
VAR _previous= MAXX(FILTER(all('Table'),'Table'[transactiondatetime]<=_time&¬(ISBLANK('Table'[Transaction Value ]))),'Table'[transactiondatetime])
return maxx(FILTER('Table','Table'[transactiondatetime]=_previous),'Table'[Transaction Value ])
please see the attachment below
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |