cancel
Showing results for
Did you mean: Frequent Visitor

## Determine the First of Two Actions

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

)

1 ACCEPTED SOLUTION  Community Support

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.

2 REPLIES 2  Community Support

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.  Super User

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")) Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (5,142)