Need help on Dax formula

[santoshfx]

I have 3 tables Requests, Users, Teams
- I want to find the working days between a Request ID with a Status as New (Created Date) and same Request ID immediate subsequent status (Reported Date)
- so [No of days] formula should be = Networkdays(Created Date, Reported Date)
- in the output Assignee and Priority should be populated from Subsequent status record.

- Finally  I want to create a chart which team wise avg no of days taken for each priority

Request

ID	Request ID	Created Date	Reported Date	Status	TxnID	Assignee	Priority
1	100	01-06-2025	05-06-2025	New	1	abc	Low
2	100	01-06-2025	10-06-2025	Assigned	2	pqr	Low
3	100	01-06-2025	11-06-2025	In Progress	3	pqr	Low
4	100	01-06-2025	12-06-2025	Closed	4	pqr	Low
6	101	05-06-2025	12-06-2025	New	8	abc	High
7	101	05-06-2025	16-06-2025	Open	2	abc	Low
8	101	05-06-2025	17-06-2025	Closed	10	abc	Low
9	102	06-06-2025	17-06-2025	New	14	xyz	High
10	102	06-06-2025	22-06-2025	Closed	16	xyz	High

Users

ID	username	team_id
1	abc	1
2	pqr	1
3	xyz	2

Teams

ID	TeamName
1	Team 1
2	Team 2

Output

Request ID	No of Days	Assignee	Priority	Team
100	7	pqr	Low	Team 1
101	8	abc	High	Team 1
102	11	xyz	High	Team 2

Dax formula tried

Output Table =

SUMMARIZECOLUMNS (

    'Requests'[Request ID],

    "No of Days",

        VAR _createdDate =

            CALCULATE (

                MIN ( 'Requests'[Created Date] ),

                ALLEXCEPT ( 'Requests', 'Requests'[Request ID] )

            )

        VAR _reportedDate =

            CALCULATE (

                MAXX ( 'Requests', 'Requests'[Reported Date] ),

                ALLEXCEPT ( 'Requests', 'Requests'[Request ID] )

            )

        RETURN

            DATEDIFF ( _createdDate, _reportedDate, DAY ),

    "Assignee",

        VAR _latestTxnId =

            CALCULATE (

                MAXX (

                    FILTER (

                        ALLEXCEPT ( 'Requests', 'Requests'[Request ID] ),

                        'Requests'[Reported Date] = CALCULATE ( MAXX ( 'Requests', 'Requests'[Reported Date] ), ALLEXCEPT ( 'Requests', 'Requests'[Request ID] ) )

                    ),

                    'Requests'[TxnId]

                )

            )

        RETURN

            LOOKUPVALUE ( 'Requests'[Assignee], 'Requests'[TxnId], _latestTxnId ),

    "Priority",

        VAR _latestTxnId =

            CALCULATE (

                MAXX (

                    FILTER (

                        ALLEXCEPT ( 'Requests', 'Requests'[Request ID] ),

                        'Requests'[Reported Date] = CALCULATE ( MAXX ( 'Requests', 'Requests'[Reported Date] ), ALLEXCEPT ( 'Requests', 'Requests'[Request ID] ) )

                    ),

                    'Requests'[TxnId]

                )

            )

        RETURN

            LOOKUPVALUE ( 'Requests'[Priority], 'Requests'[TxnId], _latestTxnId ),

    "Team",

        VAR _latestTxnId =

            CALCULATE (

                MAXX (

                    FILTER (

                        ALLEXCEPT ( 'Requests', 'Requests'[Request ID] ),

                        'Requests'[Reported Date] = CALCULATE ( MAXX ( 'Requests', 'Requests'[Reported Date] ), ALLEXCEPT ( 'Requests', 'Requests'[Request ID] ) )

                    ),

                    'Requests'[TxnId]

                )

            )

        VAR _finalAssignee =

            LOOKUPVALUE ( 'Requests'[Assignee], 'Requests'[TxnId], _latestTxnId )

        RETURN

            LOOKUPVALUE (

                Teams[TeamName],

                Teams[ID], LOOKUPVALUE (

                    Users[team_id],

                    Users[username], _finalAssignee

                )

            )

)

[v-kpoloju-msft]

Hi @santoshfx,

Thank you for reaching out to the Microsoft fabric community forum.  I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

outcome:

For 101, the immediate subsequent row will be with ID 8. This is why the priority is set to low.

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[lbendlin]

Please provide more details.  What do you consider working days and do you have a holiday list?

What should happen when assignees change?  Show first or last one?

What should happen when the priority changes?  Show highest one or last one?

[santoshfx]

When a new request is created it has Status as New
Later on it can have any other status like (Assigned, In Progress, Open, Closed, Rejected) and there is no sequence

No of working days should be calculated based on Created Date when the status was New and the Date reported when status first got changed.

In the output, I want to see Request ID, No of Days.
Additionally, Assignee, Priority & Team (details from changed record)