Calculate days spent in the same status - date in same column

[tryingmybestIE]

Hi, 

First of all, I'd like to apologise, I'm very new to PowerBi, I've tried to look at previous similar posts and their solution, but couldn't make them work for my case. We are trying to get more insights into our sales process. We have different stages (we call them status) for our sales opportunities. The statuses are 1. Preparation, 2. Discussion with Prospect, 3. Submitted, 4. Accepted, 5. Completed,   6. Declined, 7. Did not proceed. Firstly, we want to understand how long (how many days) the opportunities stay in each status. Then, we want to identify those opportunities that have been in the current status for more than 180 days. 

For the first request (days in each status) i'm trying to create a calculated column, but until now I haven't been able to reach the results I want.  This is a sample of what i have (table name is opportunity_ status)

opportunity_id	start_date	status
13	11/03/2026	5. Completed
13	07/01/2026	4. Accepted
13	27/06/2025	3. Submitted
13	05/03/2025	2. Discussion with Prospect
13	05/11/2024
24	22/08/2025	6. Declined
24	04/12/2024	2. Discussion with Prospect
24	29/07/2024	7. Did not proceed
24	14/07/2024	2. Discussion with Prospect
24	16/03/2024	7. Did not proceed
24	16/03/2024	2. Discussion with Prospect
24	14/03/2024
51	17/08/2025	7. Did not proceed
51	01/08/2025
79	27/01/2026	6. Declined
79	14/01/2026	2. Discussion with Prospect
103	11/02/2026	3. Submitted
103	10/02/2026	2. Discussion with Prospect
103	08/02/2026	1. Preparation

and this is the result I'd like to have

opportunity_id	start_date	status	days_in_status
13	11/03/2026	5. Completed	5
13	07/01/2026	4. Accepted	63
13	27/06/2025	3. Submitted	194
13	05/03/2025	2. Discussion with Prospect	114
13	05/11/2024		120
24	22/08/2025	6. Declined	206
24	04/12/2024	2. Discussion with Prospect	261
24	29/07/2024	7. Did not proceed	128
24	14/07/2024	2. Discussion with Prospect	15
24	16/03/2024	7. Did not proceed	120
24	16/03/2024	2. Discussion with Prospect	0
24	14/03/2024		2
51	17/08/2025	7. Did not proceed	211
51	01/08/2025		16
79	27/01/2026	6. Declined	48
79	14/01/2026	2. Discussion with Prospect	13
103	11/02/2026	3. Submitted	33
103	10/02/2026	2. Discussion with Prospect	1
103	08/02/2026	1. Preparation	2

so, for the latest status the calculation should be days since start_date to today.  and for the rest, days since start_date to start_date of next status. Does it makes sense? Can somebody please help me with DAX for a calculated column like that? 

Then, I think I'd also need another measure, I was thinking maybe a true/false? something like overdue_in_ status, that is true is value in Days_in_status > 180 for the latest status, if latest status is Preparation, Discussion with Prospect, Submitted or Accepted. Or is there a better way to identify opportunities that have been in the current status for longer that 180 days?

Thank you so much

[v-sshirivolu]

Hi @tryingmybestIE ,
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.

[tryingmybestIE]

Hi @jojojojojo @wardy912 @ThxAlot @mizan2390 , thank you so much for your responses. I’ll take a couple of days to test your solutions, as managing reports in Power BI isn’t my main responsibility and I can only work on it when I don’t have more urgent tasks. 

I wanted to mention one thing- and @mizan2390 response made me thing about it: opportunities can also go back in status - so go from status 1 to status 3, and then go back to status 2, for instance. Just in case this is relevant. 

[mizan2390]

hi @tryingmybestIE, The good news is that if the backward movement happens on different days, the current DAX formula will still work perfectly.

If you look into the image, you would find that for opportunity id= 24, its working fine. 
This is what the DAX would handle:

1. If an opportunity is in "3. Submitted" on Monday, and goes backward to "2. Discussion" on Thursday, the DAX just looks for the next chronological date. Since Thursday comes after Monday, it will correctly identify the move to Step 2 as the next status and calculate the days perfectly.

2. If an opportunity moves from Step 3 backward to Step 2 on the exact same day, the DAX formula currently assumes it went from 2 to 3 (standard progression) because it uses the step number as a tie-breaker.

So, if opportunities jump backward and forward on the exact same day, there is logically no way for Power BI to know which one happened first without a time stamp. 
So to work with the DAX for this scenario, you would need to either
1. Add a time stamp along with the date to identify the future step
or

2. You need to explicitly add an Index Column in Power Query or in the source data to give Power BI to identify the row-by-row chronological order.
Hope this would clarify your doubt.

[tryingmybestIE · Accepted solution]

Thank you! Change of status on the same day occurs very rarely, so I think it's ok to implement a solution that don't take them into account

[mizan2390]

hi @tryingmybestIE 

I noticed in your data that you have few steps that are happening in the same date. So without an explicit index or order column, standard DAX date comparisons won't know which status came first. However, since your status names already include a number (e.g., "1. Preparation", "2. Discussion with Prospect"), we can extract that number and use it as a tie-breaker.

Step_Number =
IFERROR(
VALUE(LEFT(opportunity_status[status], FIND(".", opportunity_status[status]) - 1)),
0
)

Now, create another calculated column for days_in_status.  This looks for the next chronological date for that specific opportunity. If the date is the exact same, it looks for a higher Step_Number. If no future step exists, it calculates the days up to TODAY().

days_in_status =
VAR CurrentID = sheet1[opportunity_id]
VAR CurrentDate = sheet1[start_date]
VAR CurrentStep = sheet1[Step_Number]
VAR FutureSteps = 
    FILTER(
        sheet1,
        sheet1[opportunity_id] = CurrentID &&
        (         
            sheet1[start_date] > CurrentDate || 
            (sheet1[start_date] = CurrentDate && sheet1[Step_Number] > CurrentStep)
        )
    )
VAR NextDate = MINX(FutureSteps, sheet1[start_date])
RETURN
    IF(
        ISBLANK(NextDate),
        DATEDIFF(CurrentDate, TODAY(), DAY),
        DATEDIFF(CurrentDate, NextDate, DAY)
    )

and lastly you want to create a calculated columnn to identify active opportunities that have been stuck in their current status for more than 180 days. for this use the same logic like earlier.

Overdue_in_Status =
VAR CurrentID = sheet1[opportunity_id]
VAR CurrentDate = sheet1[start_date]
VAR CurrentStep = sheet1[Step_Number]
VAR FutureSteps = 
    FILTER(
        sheet1,
        sheet1[opportunity_id] = CurrentID &&
        (
            sheet1[start_date] > CurrentDate || 
            (sheet1[start_date] = CurrentDate && sheet1[Step_Number] > CurrentStep)
        )
    )
VAR NextDate = MINX(FutureSteps, sheet1[start_date])
VAR IsLatestStatus = ISBLANK(NextDate)
VAR ActiveStatuses = {
    "1. Preparation", 
    "2. Discussion with Prospect", 
    "3. Submitted", 
    "4. Accepted
}
RETURN
IF(
    IsLatestStatus = TRUE() && 
    sheet1[status] IN ActiveStatuses && 
    sheet1[days_in_status] > 180,
    TRUE(),
    FALSE()
)

If this solves your problem please mark it as solved. 

[v-sshirivolu]

Hi @tryingmybestIE ,

I would take a moment to thank @wardy912  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

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

[ThxAlot]

[mizan2390]

With this the problem would be if theres any step at the same date. Like oppurtunity_id on 3/16/2024

[wardy912]

Hi @tryingmybestIE 

 First create a calculated column to find the days in each status as follows:

Days_in_status =
VAR CurrentOpp = opportunity_status[opportunity_id]
VAR CurrentDate = opportunity_status[start_date]

VAR NextDate =
    CALCULATE(
        MIN(opportunity_status[start_date]),
        FILTER(
            opportunity_status,
            opportunity_status[opportunity_id] = CurrentOpp &&
            opportunity_status[start_date] > CurrentDate
        )
    )

RETURN
IF(
    NOT ISBLANK(NextDate),
    NextDate - CurrentDate,
    TODAY() - CurrentDate
)

Next, a column that identifies the latest status

Is_latest_status =
VAR CurrentOpp = opportunity_status[opportunity_id]
VAR CurrentDate = opportunity_status[start_date]

VAR MaxDate =
    CALCULATE(
        MAX(opportunity_status[start_date]),
        FILTER(
            opportunity_status,
            opportunity_status[opportunity_id] = CurrentOpp
        )
    )
RETURN
CurrentDate = MaxDate

Finally, a column that provides an overdue flag

Overdue_in_status =
IF(
    opportunity_status[Is_latest_status] = TRUE()
        && opportunity_status[Days_in_status] > 180
        && opportunity_status[status] IN {
            "1. Preparation",
            "2. Discussion with Prospect",
            "3. Submitted",
            "4. Accepted"
        },
    TRUE(),
    FALSE()
)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[johnt75]

You can create a calculated column like

Days In Status = VAR NextDate =
SELECTCOLUMNS(
    OFFSET(
        1,
        ALL( 'opportunity_ status'[opportunity_id], 'opportunity_ status'[start_date], 'opportunity_ status'[status] ),
        ORDERBY( 'opportunity_ status'[start_date], ASC, 'opportunity_ status'[status], ASC ),
        PARTITIONBY( 'opportunity_ status'[opportunity_id] )
    ),
    'opportunity_ status'[start_date]
)
VAR Result = DATEDIFF( 
    'opportunity_ status'[start_date],
    COALESCE( NextDate, TODAY()),
    DAY
)
RETURN Result

and then create a measure like

> 180 days =
VAR LatestStatuses =
    INDEX (
        1,
        ALL ( 'opportunity_ status' ),
        ORDERBY (
            'opportunity_ status'[start_date], DESC,
            'opportunity_ status'[status], DESC
        ),
        PARTITIONBY ( 'opportunity_ status'[opportunity_id] ),
        MATCHBY (
            'opportunity_ status'[opportunity_id],
            'opportunity_ status'[start_date],
            'opportunity_ status'[status]
        )
    )
VAR Result =
    COUNTROWS (
        FILTER (
            KEEPFILTERS ( LatestStatuses ),
            'opportunity_ status'[status]
                IN {
                    "1. Preparation",
                    "2. Discussion with Prospect",
                    "3. Submitted",
                    "4. Accepted"
                }
                    && 'opportunity_ status'[Days In Status] > 180
        )
    )
RETURN
    Result

If you put the measure in a table visual with the opportunity id it will only show those opportunities matching your criteria, or you can use the measure on its own in a card visual to give the total number of opportunities matching.