Forum Discussion
Date difference in processing at various phases
Hi Experts,
I have about 50 dates in the system from various tables. and I need to find the processing time in days between each VALID time. one way is in T_SQL write 50 times DateDiff or use M or Dax.
With a suggestion where is this best written and also If this is M or Dax please can you advise me how to get achieve this,
based on this, i need to write a measure, how many records in different phasses. The following are given in order, but there can be NULL or invalid date any time or system not updated.
I found this function in sqlbi
Now if you advise to do this in DAX, then can we have a loop to find a date in row and for each row find the day difference beween two valid dates ?
Days in period :=
INT ( LASTDATE ( 'Date'[Date] ) - FIRSTDATE ( 'Date'[Date] ) )
| Dates of Interest | Dates in format (DD/MM/YYYY) | |
| 1 | Date EOI | 12/10/2021 |
| 2 | Date EOI ProcessingStarted | 15/10/2021 |
| 3 | Date Delegated officer Issued | 15/10/2021 |
| 4 | Date Cancelled 1 | |
| 5 | Date Considered for Competition | 18/10/2021 |
| 6 | Date for Filing | |
| 7 | Date Cancelled 2 | |
| 8 | Date of League match Held | 25/10/2021 |
| 9 | Date of Inter league held |
The list is long for about 50-60 daytime
2 Replies
Anonymous , Assume have date in rows and you want to take diff with date in last row for same ID say
new column =var _max = maxx(filter(Table, [ID] = earlier([ID]) && [Date] < earlier([Date]) ), [Date] )
return
datediff(_max,[Date], day)Hi amitchandak Yes, I am pulling data from various tables in sql script so should be for each record ID in a row but I am not looking at just days beween max and min, I am looking at transition time between each valid date and map the phase with a look up table which will be given to me later next week.
Also I am not sure, if I understand to map the expression with my 50 days in a row ...
1. [ID] = earlier([ID]) && [Date] < earlier([Date])
2. Is the third parameter output [Date] ?
I was looking for a loop to generate a diff between each date and I will use your formula to show the max days it took from phase 1 to phasse 2 of the system .
