Forum Discussion
First date based on value
Hey,
I think this should be an easy one. Work with R&D data.
I am trying to flag the first date in which a product moved through stages of development.
The data contains dates, product codes and stages. Looks like this:
| Date | Product code | Stage |
| 21-11-19 | Test1 | A |
| 21-11-19 | Test2 | A |
| 21-11-19 | Test3 | A |
| 21-11-19 | Test4 | A |
| 22-11-19 | Test1 | A |
| 22-11-19 | Test2 | B |
| 22-11-19 | Test3 | A |
| 22-11-19 | Test4 | A |
| 23-11-19 | Test1 | A |
| 23-11-19 | Test2 | B |
| 23-11-19 | Test3 | B |
| 23-11-19 | Test4 | A |
And I would like a column that returned this:
| Date | Product code | Stage | First date on stage B |
| 21-11-19 | Test1 | A | null |
| 21-11-19 | Test2 | A | null |
| 21-11-19 | Test3 | A | null |
| 21-11-19 | Test4 | A | null |
| 22-11-19 | Test1 | A | null |
| 22-11-19 | Test2 | B | 22-11-19 |
| 22-11-19 | Test3 | A | null |
| 22-11-19 | Test4 | A | null |
| 23-11-19 | Test1 | A | null |
| 23-11-19 | Test2 | C | 22-11-19 |
| 23-11-19 | Test3 | B | 23-11-19 |
| 23-11-19 | Test4 | A | null |
I guess it has to involve firstdate function in DAX, but nothing I've tried so far has worked at all.
Any help would be appreciated.
Thanks!
Anonymous
You would need to add some conditions if you want get the First Change of the Stage (to Stage B):
Result = VAR previousstage = CALCULATE ( MIN ( 'Table'[Stage] ), ALLEXCEPT ( 'Table', 'Table'[Product code] ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) ) VAR FirstChangeToB = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Stage] = "B" ), FILTER ( 'Table', 'Table'[Product code] = EARLIER ( 'Table'[Product code] ) ) ) RETURN IF ( NOT ISBLANK ( previousstage ) && previousstage <> 'Table'[Stage], FirstChangeToB, BLANK () )
Best,
Paul
2 Replies
Hi Anonymous ,
Here's a quick and dirty version to get you started. I think there's a better way of accomplishing this but I haven't had my coffee this morning yet. As a calculated column:Date of Change = var _FirstDate = CALCULATE(MIN([Date]), FILTER(RnD, [Stage] < EARLIER([Stage]) && [Product code] = EARLIER([Product code]))) var _SecondDate = CALCULATE(MIN([Date]), FILTER(RnD, [Stage] = EARLIER([Stage]) && [Product code] = EARLIER([Product code]))) RETURN IF(NOT ISBLANK(_FirstDate) && _SecondDate = [Date], [Date], BLANK())Anonymous
You would need to add some conditions if you want get the First Change of the Stage (to Stage B):
Result = VAR previousstage = CALCULATE ( MIN ( 'Table'[Stage] ), ALLEXCEPT ( 'Table', 'Table'[Product code] ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) ) VAR FirstChangeToB = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Stage] = "B" ), FILTER ( 'Table', 'Table'[Product code] = EARLIER ( 'Table'[Product code] ) ) ) RETURN IF ( NOT ISBLANK ( previousstage ) && previousstage <> 'Table'[Stage], FirstChangeToB, BLANK () )
Best,
Paul
