max date conditional

[exodeexo]

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

[Phil_Seamark]

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 🙂

[exodeexo]

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! 

[Phil_Seamark]

HI @exodeexo

Why are the top two rows NULL, when there are other rows for 136411 further down?

[exodeexo]

Excuse me @Phil_Seamark!

I made a mistake, the two first rows should be 13/10/2015 if the campaign id is equals 136411. I've forgotten to change the campaign ID in the two first rows.

[Phil_Seamark]

Cool, I nearly have it.  Just one more question.

On that same campaign 136411, how come all the dates are the 13th, except for the last one which is the 14th of Oct?

[exodeexo]

Sorry @Phil_Seamark. It was another mistake that I've modified. The expected result for campaign ID 136411 is 14/10/2015.

The correct table:

Area	Process	Start Sequence	Campaign ID	MAX_DATE
WH	COD	29/07/2015	155555	null
WH	PREP	30/07/2015	155555	null
WH	PREP	30/07/2015	135871	null
PRODUCT	DESC_IT	28/08/2015	135871	null
WH	PREP	30/07/2015	136411	14/10/2015
PRODUCT	DESC_IT	19/08/2015	136411	14/10/2015
PHOTO	PHTM	13/10/2015	136411	14/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

[Phil_Seamark · Accepted solution]

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])

[davidataylor]

This came in very handy for me today.  Thanks for working through this in great detail 2 years ago!

[exodeexo]

You are a genius,@Phil_Seamark!

It works!!! You save me a lot of time!!! Because I've spent all my day trying to solve it!

Many thanks!!!!

[Phil_Seamark]

Happy to help 🙂

[exodeexo]

Excuse me @Phil_Seamark

The correct table is:

Area	Process	Start Sequence	Campaign ID	MAX_DATE
WH	COD	29/07/2015	155555	null
WH	PREP	30/07/2015	155555	null
WH	PREP	30/07/2015	135871	null
PRODUCT	DESC_IT	28/08/2015	135871	null
WH	PREP	30/07/2015	136411	14/10/2015
PRODUCT	DESC_IT	19/08/2015	136411	14/10/2015
PHOTO	PHTM	13/10/2015	136411	14/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