Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to create a column in DAX that calculates the time taken for a ticket from it initiated to it gets done. as shown below:
Could you please help me with that?
Thanks
Solved! Go to Solution.
Hi @Bukha
You may create a rank column first. Then you may get the time taken column as below.
Rank = RANKX ( FILTER ( 'Sample', 'Sample'[OrderID] = EARLIER ( 'Sample'[OrderID] ) ), 'Sample'[Date], , ASC )
Time taken(day) = VAR Pre_Date = CALCULATE ( MAX ( 'Sample'[Date] ), FILTER ( ALLEXCEPT ( 'Sample', 'Sample'[OrderID] ), 'Sample'[Rank] = EARLIER ( 'Sample'[Rank] ) - 1 ) ) RETURN IF ( 'Sample'[Status] = "Open", BLANK (), DATEDIFF ( Pre_Date, 'Sample'[Date], DAY ) )
Regards,
Cherie
Hi @Bukha
You may create a rank column first. Then you may get the time taken column as below.
Rank = RANKX ( FILTER ( 'Sample', 'Sample'[OrderID] = EARLIER ( 'Sample'[OrderID] ) ), 'Sample'[Date], , ASC )
Time taken(day) = VAR Pre_Date = CALCULATE ( MAX ( 'Sample'[Date] ), FILTER ( ALLEXCEPT ( 'Sample', 'Sample'[OrderID] ), 'Sample'[Rank] = EARLIER ( 'Sample'[Rank] ) - 1 ) ) RETURN IF ( 'Sample'[Status] = "Open", BLANK (), DATEDIFF ( Pre_Date, 'Sample'[Date], DAY ) )
Regards,
Cherie
The DAX has been improved. Hope it's better now.
Thanks and BR
Ryan
Proud to be a Super User!
Hi , I have another solution for this, but I think it may not be the best one.
It's becuase you need to filter open status to see the correct date difference.
Hope this is helpful.
Thanks and BR
Ryan
Proud to be a Super User!
Hi @Bukha
If you can work on your ETL, I suggest you using an Accumulating Snapshot for this scenario and turn the data into:
more info: https://dwbi1.wordpress.com/2011/08/09/using-accumulating-snapshot-fact-table-to-monitor-status/
You can add the column like this by the way:
= IF ( Orders[Status] = "Open", BLANK (), Orders[Date] - CALCULATE ( MAX ( Orders[Date] ), ALLEXCEPT ( orders, Orders[Order ID] ), Orders[Status] = SWITCH ( EARLIER ( Orders[Status] ), "Done", "Under Review", "Under Review", "In Progress", "In Progress", "Open" ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |