Generate New table or calculated columns based on column and rows

[lyndaGough]

I am very new to PowerBi and I am learning how to do the things I want by finding answers to similar issues raised in the forums and on blogs/videos.

I need to work out a period (start date and end date – in calculated columns) based on data from different rows and columns. I can do what I am after in nested if statements in Excel but I am struggling to work out how to do the same in PowerBI.

Scenario:
The data is for children whereby regular meetings occur. I need to identify a period (Safer Me CIN) based on an outcome of those meetings and the dates of those meetings.

Data structure:
Table - The meeting details come from answers filled in forms brought into PowerBi in ‘tbl_007SaferMeCIN – Form Details’. This table is linked (many - one) to the Person table ‘tbl_004_PersonView’

Relationship - tbl_004_PersonView[Eclipse ID] = tbl_007SaferMeCIN – Form Details[PERid]

Column Names = [PERid], [CALC_001 Date of Meeting], [CIN Meeting Number], [CALC_002 Safer Me CIN], [CIN Meeting Number]
Desired 2 Columns: [Start Date], [End Date]

Note – 3 of these columns are calculated columns – 2 as required to get the data from the source database and 1 to rank the meetings.

Logic:
Start Date = the date of the first meeting (CALC_001 Date of meeting) where the outcome (CALC-002 Safer Me CIN) = “Yes”. The child remains “Safer Me CIN” when the next meeting outcome remains “Yes”.
End Date = the date of the meeting minus 1 where the outcome is “No”.
Where the outcome of the meeting is “No” the child is not classed as in a period of Safer Me CIN (blank()).

Desired Outcome:
Originally I was wanting just 2 added calculated columns on the same table as the meeting data but thinking more logically, I would really like this data to generate a new table that can be linked back to the individual child (PersonView table).

New Table Desired Effect

PER ID	Start Date	End Date
1001	19/08/2020	17/09/2020
1002	07/12/2020	21/04/2021
1004	01/10/2020	21/10/2020
1004	16/11/2020	05/01/2021
1005	26/05/2021	Blank()
1006	29/07/2020	Blank()

Desired Effect if new calculated columns in original table

PER ID	CALC_001 Date of Meeting	CIN Meeting Number	CALC_002 Safer Me CIN	Start Date	End Date
1001	19/08/2020	1	Yes	19/08/2020	17/09/2020
1001	18/09/2020	2	No	Blank()	Blank()
1001	28/10/2020	3	No	Blank()	Blank()
1002	07/12/2020	1	Yes	07/12/2020	21/04/2021
1002	13/03/2021	2	Yes	07/12/2020	21/04/2021
1002	22/04/2021	3	No	Blank()	Blank()
1003	02/09/2020	1	No	Blank()	Blank()
1004	01/10/2020	1	Yes	01/10/2020	21/10/2020
1004	22/10/2020	2	No	Blank()	Blank()
1004	16/11/2020	3	Yes	16/11/2020	05/01/2021
1004	06/01/2021	4	No	Blank()	Blank()
1005	27/04/2021	1	No	Blank()	Blank()
1005	26/05/2021	2	Yes	26/05/2021	Blank()
1005	16/06/2021	3	Yes	26/05/2021	Blank()
1006	29/07/2020	1	Yes	29/07/2020	Blank()
1006	26/08/2020	2	Yes	29/07/2020	Blank()
1006	22/09/2020	3	Yes	29/07/2020	Blank()
1006	09/03/2021	4	Yes	29/07/2020	Blank()

Formula used in Excel:
Start Date - =IF(AND(A3<>A2,D3="Yes"),B3,IF(AND(A3=A2,D2="No", D3="Yes"), B3,IF(AND(A3=A2, D2="Yes", D3="Yes"),H2,IF(D3="No","","Check"))))
End Date - =IF(AND(A3=A4, D3="Yes",D4="No"),(B4)-1,IF(AND(A3<> A4, D3="Yes"),"",IF(AND(A3=A4, D3="Yes", D4="Yes"),I4,IF(D3="No","","Check"))))

Note – “check” only there to spot error in formula.

[DataInsights · Accepted solution]

@lyndaGough,

This solution uses both calculated columns and a calculated table.

1. Create calculated column [Start Date]:

Start Date = 
VAR vPerID = 'tbl_007SaferMeCIN – Form Details'[PER ID]
VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting]
VAR vFirstMeetingDateOutcomeYes =
    CALCULATE (
        MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes"
    )
VAR vPrevMeetingDate =
    CALCULATE (
        MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate
    )
VAR vPrevMeetingDateOutcome =
    CALCULATE (
        MAX ( 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] = vPrevMeetingDate
    )
VAR vCountOutcomeNo =
    COUNTROWS (
        FILTER (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID] = vPerID
                && 'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No"
                && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vFirstMeetingDateOutcomeYes
                && 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] < vMeetingDate
        )
    )
VAR vResult =
    SWITCH (
        TRUE (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No", BLANK (),
        ISBLANK ( vCountOutcomeNo ), vFirstMeetingDateOutcomeYes,
        vPrevMeetingDateOutcome = "No", vMeetingDate
    )
RETURN
    vResult

2. Create calculated column [End Date]:

End Date = 
VAR vMeetingDate = 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting]
VAR vNextMeetingDateOutcomeNo =
    CALCULATE (
        MIN ( 'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] ),
        ALLEXCEPT (
            'tbl_007SaferMeCIN – Form Details',
            'tbl_007SaferMeCIN – Form Details'[PER ID]
        ),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No",
        'tbl_007SaferMeCIN – Form Details'[CALC_001 Date of Meeting] > vMeetingDate
    )
VAR vResult =
    SWITCH (
        TRUE (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "No"
            || ISBLANK ( vNextMeetingDateOutcomeNo ), BLANK (),
        'tbl_007SaferMeCIN – Form Details'[CALC_002 Safer Me CIN] = "Yes", vNextMeetingDateOutcomeNo - 1
    )
RETURN
   vResult

3. Create calculated table:

New Table = 
CALCULATETABLE (
    SUMMARIZE (
        'tbl_007SaferMeCIN – Form Details',
        'tbl_007SaferMeCIN – Form Details'[PER ID],
        'tbl_007SaferMeCIN – Form Details'[Start Date],
        'tbl_007SaferMeCIN – Form Details'[End Date]
    ),
    NOT ( ISBLANK ( 'tbl_007SaferMeCIN – Form Details'[Start Date] ) )
)

[lyndaGough]

Thank you so much for this.  It seems to be working beautifully.  Now I will study the code to learn how it works so I can write this style of code in the future.😁