Count from status A to B given a column value and days in between

[jbrooi]

Hi all,

Can't seem to solve this - I want to count the candidates that move from 'Job Application Created' with a Media Campaign form to 'Placement Created'. And only if that's in a time period of max. 60 days.

What I tried is

Social count = 
VAR _Start = MAXX(  FILTER((Activities), Activities[act_candidate_sfid] = MAX(Activities[act_candidate_sfid]) && Activities[act_job_application_form_name]="Media Campaign form" && Activities[act_description]="Job Application Created"), Activities[act_job_application_created_date])
VAR _End = MAXX( FILTER((Activities), Activities[act_candidate_sfid] = MAX(Activities[act_candidate_sfid]) && Activities[act_description]="Placement Created"), Activities[act_job_application_created_date])
RETURN
CALCULATE(
    DISTINCTCOUNT(Activities[act_candidate_sfid]),
    FILTER(Activities,
    Activities[act_candidate_sfid] = MAX(Activities[act_candidate_sfid])
            && DATEDIFF(_Start, _End, DAY)<60
))

In this dataset there's 2 counts for a valid sequence like described. There are multiple Job Applications Created per candidate but it's about those with a media campaign form and who are followed up by a Placement created. 

Adding data table via wetransfer:

https://we.tl/t-z2iI3a5zXT

Thanks! 

Jan 

[v-jianboli-msft · Accepted solution]

Hi @jbrooi ,

Please try:

Measure =
VAR _a =
    FILTER (
        Activities,
        ( Activities[act_job_application_form_name] = "Media Campaign form"
            && Activities[act_description] = "Job Application Created" )
            || ( Activities[act_description] = "Placement Created" )
    )
VAR _b =
    SUMMARIZE ( _a, [act_candidate_sfid] )
VAR _c =
    ADDCOLUMNS (
        _b,
        "StartDate",
            MAXX (
                FILTER (
                    Activities,
                    [act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
                        && ( Activities[act_job_application_form_name] = "Media Campaign form"
                        && Activities[act_description] = "Job Application Created" )
                ),
                [act_job_application_created_date]
            ),
        "EndDate",
            MAXX (
                FILTER (
                    Activities,
                    [act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
                        && ( Activities[act_description] = "Placement Created" )
                ),
                [act_job_application_created_date]
            )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Activities[act_candidate_sfid] ),
        FILTER ( _c, DATEDIFF ( [StartDate], [EndDate], DAY ) <= 60 )
    )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jbrooi]

Thanks, much appreciated @v-jianboli-msft !

After reviewing I had to add statements for an Index field I have in the dataset, to check for multiple batches of Applications and Placements - and not just the last one (from the MAXX).

I'm adding the code here for reference:

Social plaatsingen = 
VAR _a =
    FILTER (
        Activities,
        ( Activities[act_job_application_form_name] = "Media Campaign form"
            && Activities[act_description] = "Job Application Created" )
            || ( Activities[act_description] = "Placement Created" )
    )
VAR _b =
    SUMMARIZE ( _a, [act_candidate_sfid] , Activities[pl_segm_index])
VAR _c =
    ADDCOLUMNS (
        _b,
        "StartDate",
            MAXX (
                FILTER (
                    Activities,
                    [act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
                    && Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
                        && ( Activities[act_job_application_form_name] = "Media Campaign form"
                        && Activities[act_description] = "Job Application Created" )
                ),
                [act_job_application_created_date]
            ),
        "EndDate",
            MAXX (
                FILTER (
                    Activities,
                    [act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
                    && Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
                        && ( Activities[act_description] = "Placement Created" )
                ),
                [act_job_application_created_date]
            ),
        "SegmentStart",
            MAXX (
                FILTER (
                    Activities,
                    [act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
                    && Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
                        && ( Activities[act_job_application_form_name] = "Media Campaign form"
                        && Activities[act_description] = "Job Application Created" )
                ),
                Activities[pl_segm_index]
            ), 
        "SegmentEnd",
            MAXX (
                FILTER (
                    Activities,
                    [act_candidate_sfid] = EARLIER ( Activities[act_candidate_sfid] )
                    && Activities[pl_segm_index] = EARLIER( Activities[pl_segm_index] )
                        && ( Activities[act_description] = "Placement Created" )
                ),
                Activities[pl_segm_index]
            )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Activities[act_candidate_sfid] ),
        FILTER ( _c, DATEDIFF ( [StartDate], [EndDate], DAY ) <= 60 && [SegmentStart]=[SegmentEnd])
    )