Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I'm hoping someone can assist with a lookup scenario.
I have one table (Image 1) with 2 id columns: op_id, prev_op_id and 2 operational DateTime fields: start_op_dt, end_op_dt.
In this image the start_op_dt and end_op_dt are for op_id
Image 1
I have to calculate the time difference between the end_op_dt of prev_op_id and start_op_dt of op_id.
I have tried duplicating the table and linking pre_op_id (duplicated table) to _op_id. Image 2 lists the data of based on duplicating and joing the 2 tables.
I then created a DAX to calculate the time difference between end_op_dt and start_op_dt. However each prev_op_id got replicated by the total number of op_ids.
Is there a way to calculate the time difference within 1 table where op_id where I can search for the end_op_dt for column prev_op_id?
Thank you.
Solved! Go to Solution.
Hi @Anonymous,
You can also try to use below formula to calculate between current id and previous id.
Calculate column:
Diff = DATEDIFF(LOOKUPVALUE([end_op_dt],[op_id],[Prev_op_id]),[start_op_dt],SECOND)
BTW, I think you should switch some correct records, I found your previous end date are greater than current start date.
Regards,
Xiaoxin Sheng
Hi @Anonymous,
I'd like to suggest you add a calculate column to store the diff value between start_date and end_date, then use it to calculate the difference of current row and previous row.
Below are sample formulas, you can refer to they if they suitable for your requirement.
calculate columns.
Diff
Diff = DATEDIFF([start_op_dt],[end_op_dt],SECOND)
Diff between current row and previous row.
Diff C - P = [Diff]-LOOKUPVALUE([Diff],[op_id],[Prev_op_id])
If you not want to use calculated column, you can try to use follow measure to calculate the diff between current and previous.
Result = MAX([Diff])-LOOKUPVALUE(Table[Diff],Table[op_id],MAX(Table[Prev_op_id]))
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Thanks for your reply.
Unfortunately, I was not getting the answer I was hoping for. I was looking to get the difference between the start_op_date for the op_id and the end_op_dt for the prev_op_id.
I modified your formula to derive my value:
CALCULATE(
1440 *
(SUM(Table1[start_op_dt]) -
CALCULATE(
(LOOKUPVALUE(Table1[end_op_dt], Table1[op_id], MAX(Table1[prev_op_id])))
)))
However, this measure gave me the result only at an id level, which is fine, but when I visulaised it at a monthly level, I got highly inflated numbers. I was after average for the month. I tried replacing SUM with AVERAGE, however this didn't work.
I finally managed to solve it by doing the following:
I duplicated the table twice:
1. Just containing op_id and prev_op_id
2. Duplication of the main table
I then joined:
* op_id (table 1) to op_id (table 2)
* prev_op_id (table 2) to op_id (table 3)
I then created a calculated column:
CALCULATE(IF(
OR(ISBLANK(SUM(Table3[end_op_dt])),
ISBLANK(SUM(Table1[start_op_dt])))
,BLANK(),
1440 * (SUM(Table1[start_op_dt]) - SUM(Table3[end_op_dt]))
))
Finally I changed the default summarisation from Sum to Average.
Thanks again for your help. I appreciate it.
Hi @Anonymous,
You can also try to use below formula to calculate between current id and previous id.
Calculate column:
Diff = DATEDIFF(LOOKUPVALUE([end_op_dt],[op_id],[Prev_op_id]),[start_op_dt],SECOND)
BTW, I think you should switch some correct records, I found your previous end date are greater than current start date.
Regards,
Xiaoxin Sheng
Thanks again for your reply.
Apologies for not replying sooner as I have on leave.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |