cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors