Determine the First of Two Actions

[PBInewbie17]

Hello, I have a table (‘1egh TSQL’) with columns of specific actions and dates on which those actions occurred.

I used the formula below to calculate a new column called Fiscal Year, based on the following criteria: 

1. Must be the MAX Sequence for the project
2. Must be the FIRST of the *Action “Application Finally Rejected” or “Application Approved” (whichever one occurs first)

• If the first two criteria apply and the *Date is 10/01/2020 – 09/30/2021, then Fiscal Year = “FY21”
• If the first two criteria apply and the *Date is 10/01/2021 – 09/30/2022, then Fiscal Year = “FY22”
• If the first two criteria apply and the *Date is 10/01/2022 – 09/30/2023, then Fiscal Year = “FY23”
• If nothing applies, then BLANK

How can I correct my formula to only select the first action that occurs? In this example, FY22 should be listed next to the *Action “Application Finally Rejected”.

Fiscal Year =

VAR _sequence = '1egh TSQL'[Sequence]

VAR _pjtnumber = '1egh TSQL'[FHA Number]

VAR _action = '1egh TSQL'[*Action]

VAR _table =

    FILTER ( '1egh TSQL', '1egh TSQL'[FHA Number] = _pjtnumber )

VAR _maxseq =

    MAXX ( _table, '1egh TSQL'[Sequence] )

RETURN

    IF (

        _sequence = _maxseq

            && ( _action = "Application Finally Rejected"

            || _action = "Application Approved" ),

        SWITCH (

            TRUE (),

            '1egh TSQL'[*Date] >= DATE ( 2020, 10, 1 )

                && '1egh TSQL'[*Date] <= DATE ( 2021, 9, 30 ), "FY21",

            '1egh TSQL'[*Date] >= DATE ( 2021, 10, 1 )

                && '1egh TSQL'[*Date] <= DATE ( 2022, 9, 30 ), "FY22",

            '1egh TSQL'[*Date] >= DATE ( 2022, 10, 1 )

                && '1egh TSQL'[*Date] <= DATE ( 2023, 9, 30 ), "FY23",

            BLANK ()

        ),

        BLANK ()

    )

[v-yinliw-msft · Accepted solution]

Hi @PBInewbie17 ,

You can try this method:

New column:

Fiscal Year =

VAR _sequence = '1egh TSQL'[Sequence]

VAR _pjtnumber = '1egh TSQL'[FHA Number]

VAR _table =

    FILTER ( '1egh TSQL', '1egh TSQL'[FHA Number] = _pjtnumber )

VAR _mindate =

    MINX (

        FILTER (

            _table,

            '1egh TSQL'[*Action]

                IN { "Application Finally Rejected", "Application Approved" }

        ),

        '1egh TSQL'[*Date]

    )

VAR _theAction =

    CALCULATE (

        MAX ( '1egh TSQL'[*Action] ),

        FILTER ( '1egh TSQL', '1egh TSQL'[*Date] = _mindate )

    )

VAR _maxseq =

    CALCULATE (

        MAX ( '1egh TSQL'[Sequence] ),

        FILTER ( '1egh TSQL', '1egh TSQL'[*Action] = _theAction )

    )

RETURN

    IF (

        '1egh TSQL'[*Action] = _theAction

            && '1egh TSQL'[Sequence] = _maxseq,

        SWITCH (

            TRUE (),

            '1egh TSQL'[*Date] >= DATE ( 2020, 10, 1 )

                && '1egh TSQL'[*Date] <= DATE ( 2021, 9, 30 ), "FY21",

            '1egh TSQL'[*Date] >= DATE ( 2021, 10, 1 )

                && '1egh TSQL'[*Date] <= DATE ( 2022, 9, 30 ), "FY22",

            '1egh TSQL'[*Date] >= DATE ( 2022, 10, 1 )

                && '1egh TSQL'[*Date] <= DATE ( 2023, 9, 30 ), "FY23",

            BLANK ()

        ),

        BLANK ()

    )

The result is:

Is this what you expect? Here is my PBIX file.

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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

[wdx223_Daniel]

Fiscal Year =

VAR _sequence = '1egh TSQL'[Sequence]

VAR _pjtnumber = '1egh TSQL'[FHA Number]

VAR _table =

    FILTER ( '1egh TSQL', '1egh TSQL'[FHA Number] = _pjtnumber )

VAR _maxseq =

    MAXX ( _table, '1egh TSQL'[Sequence] )

VAR _mindate =

    MINX ( FILTER(_table,'1egh TSQL'[*Action] IN {"Application Finally Rejected","Application Approved"}), '1egh TSQL'[*Date] )

RETURN

    IF (_sequence = _maxseq&& _mindate,FORMAT(_mindate,"F\Yyy"))