cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.  Helper I

## 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 #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator. #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (4,065)