Forum Discussion
max date conditional
Hi guys!!
I'd like to create a column (called "MAX_DATE") where I identify the max date in two specific process (PHTM & PHTS) per campaign. If these processes doesn't exist in the campaign, I'd like to asign a null. If one of them exist (PHTM or PHTS), the max_date should be the latest date. As you could see, you could have more than 1 row per campaign and process.
| Area | Process | Start Sequence | Campaign ID | MAX_DATE |
| WH | COD | 29/07/2015 | 136411 | null |
| WH | PREP | 30/07/2015 | 136411 | null |
| WH | PREP | 30/07/2015 | 135871 | null |
| PRODUCT | DESC_IT | 28/08/2015 | 135871 | null |
| WH | PREP | 30/07/2015 | 136411 | 13/10/2015 |
| PRODUCT | DESC_IT | 19/08/2015 | 136411 | 13/10/2015 |
| PHOTO | PHTM | 13/10/2015 | 136411 | 13/10/2015 |
| PHOTO | PHTS | 14/10/2015 | 136411 | 14/10/2015 |
| WH | PREP | 19/10/2015 | 127861 | 23/10/2015 |
| PHOTO | PHTM | 23/10/2015 | 127861 | 23/10/2015 |
| RETOUCH | BR24 | 14/10/2015 | 127861 | 23/10/2015 |
| RETOUCH | PROD | 14/10/2015 | 127861 | 23/10/2015 |
| PHOTO | PHTS | 13/10/2015 | 127861 | 23/10/2015 |
| PRODUCT | DESC_IT | 21/10/2015 | 127861 | 23/10/2015 |
| PRODUCT | UPDATA_IT | 15/10/2015 | 127861 | 23/10/2015 |
| PRODUCT | UPDATA_IT | 19/10/2015 | 127861 | 23/10/2015 |
| PHOTO | PHTM | 21/10/2015 | 127861 | 23/10/2015 |
I've tried with the following formula, but the result is incorrect because all the campaign ID has date (this is impossible because there are some campaigns without process equals to PHTM or PHTS):
MAX_DATE = CALCULATE(MAX(Sequencer[Start Sequence].[Date]);Sequencer[Campaign ID];Sequencer[Process]="PHTS";Sequencer[Process]="PHTM")
Could you please help me?
Many thanks!!
Ivann
HI exodeexo
Please try this calculated column
New Column = MAXX( FILTER( 'Sequencer', 'Sequencer'[Process] IN {"PHTS","PHTM"} && 'Sequencer'[Campaign ID] = EARLIER('Sequencer'[Campaign ID]) ), 'Sequencer'[Start Sequence])
11 Replies
Hi exodeexo
Happy to help. Can you please post what your expected result would look like for this sample dataset?
This will help clarify your request :)
Hi Phil_Seamark!! Thanks for helping me.
My expected result for this sample is the last column on your right.
MAX DATE null null null null 13/10/2015 13/10/2015 13/10/2015 14/10/2015 23/10/2015 23/10/2015 23/10/2015 23/10/2015 23/10/2015 23/10/2015 23/10/2015 23/10/2015 23/10/2015 Many thanks!
