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

                )

            )

)

[Poojara_D12]

Hi @santoshfx 

You're working on analyzing the turnaround time of requests in Power BI, specifically measuring how long it takes from when a request is created with a "New" status to when it moves to the next status. Your dataset includes multiple records for the same Request ID, each representing a different status change over time, with associated dates, assignees, and priorities. You want to calculate the number of working days (excluding weekends) between the "New" status and its immediate next status, and then capture the Assignee, Priority, and corresponding Team from that next status record. To visualize this, you plan to create a chart that shows the average number of working days taken by each team, broken down by request Priority.

To achieve this, you've written a complex DAX measure using SUMMARIZECOLUMNS and variables that try to identify the earliest and latest reported dates for each request. However, your logic currently fetches the latest status record, not necessarily the immediate one following "New", which skews the working days calculation. Also, your use of DATEDIFF counts total calendar days instead of working days, so it's not accurately reflecting business time. Furthermore, the values for Assignee, Priority, and Team are pulled from the wrong status update because they're linked to the record with the maximum Reported Date, not the next one after "New". For this scenario to be accurate, you need to explicitly identify the "New" status record and then find the one with the next highest TxnID or Reported Date for the same Request ID, and use these two rows to calculate the number of working days (using a working calendar). This will allow you to create a reliable summary table, which you can then use in your chart to display team-wise performance on request handling across different priorities.

[v-kpoloju-msft · Accepted solution]

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.

[v-kpoloju-msft]

Hi @santoshfx,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @santoshfx,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @santoshfx,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

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