Forum Discussion
DAX Formula for MIN Date
Hello Community,
I have Table 1 and the goal is to create a column of the Minimum [TransDateTime] for when the Service Order Line went into a "completed stage" in the "NewValue" column. The "completed stages" in the newvalue column would be either "PaperworkReview","WaitingforPaperwork", or "Finished".
Service order may not directly go in sequence. It may go directly into "Finished" and skip the paperworkreview or waitingforpaperwork value.
Table 1
| ServiceOrderLineId | ID | ServiceOrderId | ServiceOrderLineNum | TransDateTime | RecId | OldValue | NewValue |
| 85 | 28319952 | 51987677 | 4 | 7/18/21 1:12 | 544544 | PaperworkReview | Finished |
| 85 | 28319953 | 51987677 | 4 | 7/17/21 1:21 | 326442 | WaitingforPaperwork | PaperworkReview |
| 85 | 28319954 | 51987677 | 4 | 7/15/21 5:41 | 234997 | InProcess | WaitingforPaperwork |
| 32 | 28319955 | 51783544 | 2 | 7/15/21 3:33 | 234991 | PaperworkReview | Finished |
| 32 | 28319956 | 51783544 | 2 | 7/15/21 3:01 | 234999 | InProcess | PaperworkReview |
Thank you for review!
1 Reply
- wdx223_Daniel
Community Champion
=var _max=calculate(max(table1[transdatetime]),allexcept(table1,table1[serviceorderlineid]) var _min=calculate(min(table1[transdatetime]),allexcept(table1,table1[serviceorderlineid]) return if(max(table1[transdatetime])=_max,"finished",if(max(table1[transdatetime])=_min,"paperworkreview","waitingforpaperwork"))
