Create a measure or column to show if all 3 fees have been paid

[harsin]

I have a table of candidates who have been placed. A fee is charged once they have been in the role for 4 weeks, for 12 weeks and then at 22 weeks. 

For example:

Candidate	Date	week
A	01/02/23	4
B	01/03/23	4
C	11/03/23	4
D	12/03/23	4
E	25/03/23	4
A	01/03/23	12
C	11/04/23	12
D	12/03/23	12
A	05/06/23	22

I need to find a way to create a measure or column to show if all 3 fees (4,12,22) have been paid, such as the below:

Candidate	Date	week	All 3 weeks?
A	01/02/23	4	No
B	01/03/23	4	No
C	11/03/23	4	No
D	12/03/23	4	No
E	25/03/23	4	No
A	01/03/23	12	No
C	11/04/23	12	No
D	12/03/23	12	No
A	05/06/23	22	Yes

My initial thought was to check if the candidate had a fee for all 3 week marks within a 22 week period but thought the way above would be best.

[ThxAlot]

Flag = 
COUNTROWS(
    WINDOW(
        1, ABS,
        0, REL,
        ALLSELECTED( PYMT[Candidate], PYMT[week] ),
        ORDERBY( PYMT[week] ),
        PARTITIONBY( PYMT[Candidate] )
    )
) = 3

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please try something like below if it suits your requirement.

Please check the below picture and the attached pbix file.

expected result CC =
VAR _t =
    SUMMARIZE (
        FILTER (
            Data,
            Data[Candidate] = EARLIER ( Data[Candidate] )
                && Data[Date] <= EARLIER ( Data[Date] )
        ),
        Data[week]
    )
VAR _conditionone = { 4 } IN _t
VAR _conditiontwo = { 12 } IN _t
VAR _conditionthree = { 22 } IN _t
RETURN
    IF ( _conditionone && _conditiontwo && _conditionthree, "Yes", "No" )

[harsin]

Thank you Jihwan_Kim, I got the below error:

Function 'CONTAINSROW' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values. So I changed your code slightly:

expected result CC =

VAR _t =

    SUMMARIZE (

        FILTER (

            'WFA Revenue',

            'WFA Revenue'[PARTICIPANT_ID] = EARLIER ( 'WFA Revenue'[PARTICIPANT_ID] )

                && 'WFA Revenue'[PAYMENT_DATE] <= EARLIER ( 'WFA Revenue'[PAYMENT_DATE] )

        ),

        'WFA Revenue'[Outcome Week]

    )

VAR _conditionone = { "4" } IN _t

VAR _conditiontwo = { "12" } IN _t

VAR _conditionthree = { "22" } IN _t

RETURN

    IF ( _conditionone && _conditiontwo && _conditionthree, "Yes", "No" )

However all results are no, there should be some yes

[Jihwan_Kim]

Hi,

please share your sample pbix file's link, and then I can try to look into it.