Calculate how many days spent in a specific status (also when still pending)

[Emoes]

Hello, I hope I can ask for some help in Power BI.

I would like to “Calculate how many days spent in a status”

I found a topic that is partly solving my issue (https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-how-many-days-spent-in-a-status-hel...)

I have a Power Bi Table (See attached Excel Dataset / PIBX file)

I would like to calculate PER Item (Either on Item ID or on Item Title) How long it was consuming in a certain status.

I would like to use the first entry point per Item as the start date (to start the calculation)

The Item would NOT always start with Status “1. Requesting”

The Item would ALWAYS end with Status “5. Closing” (And even sometimes the Status “5. Closing” is applied twice so it always should use the LAST “5. Closing” date.

See attached Excel for dataset.

In this example:

1. Requesting	135	C	10-3-2022 00:00
1. Requesting	135	C	22-3-2022 00:00
1. Requesting	135	C	24-3-2022 00:00
4. Under Review	135	C	29-3-2022 00:00
4. Under Review	135	C	14-4-2022 00:00
4. Under Review	135	C	26-4-2022 00:00
5. Closing	135	C	27-4-2022 00:00
5. Closing	135	C	10-5-2022 00:00

In this example, I would like to report that Item “C” was in the “1. Requesting” status for 19 days, 29 days “4. Under review” And between start and end it took 61 days.

But in another example:

1. Requesting	136	J	10-3-2022 00:00
1. Requesting	136	J	11-4-2022 00:00
1. Requesting	136	J	14-4-2022 00:00
1. Requesting	136	J	12-5-2022 00:00
1. Requesting	136	J	23-5-2022 00:00

Item “J” is in “1. Requesting” state for 32, 3, 28, 11, (and based on TODAY = 28-06-2022) 36 = in total 110 days (in “1. Requesting”)

1. Requesting

145

A

30-5-2022 00:00

This item is Based on today) 29 days in the Requesting state

You will see status 2 is missing but will be added in the future.

PBIX TEST File can be found here. (Dropbox)

 Emoes

[Emoes]

Is there anybody who would like to help me with this topic (maybe one on one via Teams)

@v-yiruan-msft Helped me to start, but I'm missing 1 step I think

Thanks, Emoes

[onurbmiguel_]

Hello Emoes

Pleas take a look at this solution 

link: 

https://1drv.ms/u/s!AkcWVrMFkXs1h5w9Hka5zj8HwshXhQ?e=3qB2y5

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

[Emoes]

Hello Onurbmiguel,

Thank you for your help.

The provided solution is not yet solving my complete issue.

I would like to know how long an Item was in a certain status.

The downside I have It is not always following in the right order (going from 1 to 5)

1. Requesting	How many days having this status	Total time between first registration (don't have to be status 1, can also start at 3 till closing.') If the item is not closed, used the Today option (because the item is still open)
2. UNKNOWN YET	How many days having this status
3. Gathering Detailed Information	How many days having this status
4. Under Review	How many days having this status
5. Closing	Last date used for calculation other status
6. Hibernating	How many days having this status

Let me take 2 examples from the PBIX file (your calculation shows: 55 days for Item "C" and 55 days for Item "J" Based on my calculation it would be:

Status	Items.Id	Items.Title	Versions.properties.Created.Element:Tekst	Days	Remark	Total Time in days
1. Requesting	135	C	10-3-2022 00:00	0	Start moment (First date found for Item.Title)	0
1. Requesting	135	C	22-3-2022 00:00	12
1. Requesting	135	C	24-3-2022 00:00	2
				5	Total Days in "1. Requesting" = 12 + 2 +5 is 19
4. Under Review	135	C	29-3-2022 00:00	0
4. Under Review	135	C	14-4-2022 00:00	16
4. Under Review	135	C	26-4-2022 00:00	12
5. Closing	135	C	27-4-2022 00:00	1
5. Closing	135	C	10-5-2022 00:00	13	Total Days in "4. Under Review" = 16 + 12 +1 + 13  is 42 (Using tha last closing date	61
1. Requesting	136	J	10-3-2022 00:00	0		0
1. Requesting	136	J	11-4-2022 00:00	32
1. Requesting	136	J	14-4-2022 00:00	3
1. Requesting	136	J	12-5-2022 00:00	28
1. Requesting	136	J	23-5-2022 00:00	11
		Today	29-6-2022	37	Total time in "1. Requesting" 32 + 3 + 28 + 11 + 37 = 111 days	111

Kind Regards, Emoes

[v-yiruan-msft]

Hi @Emoes ,

I updated your sample pbix file(see attachment), please check if that is what you want.

1. Update the formula of calculated column [DaysInSinceStart] as below

DaysInSinceStart = 
VAR _predate =
    CALCULATE (
        MAX ( 'Test Data'[Versions.properties.Created.Element:Text] ),
        FILTER (
            'Test Data',
            'Test Data'[Items.Id] = EARLIER ( 'Test Data'[Items.Id] )
                && 'Test Data'[Versions.properties.Created.Element:Text]
                    < EARLIER ( 'Test Data'[Versions.properties.Created.Element:Text] )
        )
    )
RETURN
    DATEDIFF (
        _predate,
        'Test Data'[Versions.properties.Created.Element:Text],
        DAY
    )

2. Update the formula of calculated column [TotalDays] as below

TotalDays = 
VAR _closingcount =
    CALCULATE (
        COUNT ( 'Test Data'[Status] ),
        FILTER (
            'Test Data',
            'Test Data'[Items.Id] = EARLIER ( 'Test Data'[Items.Id] )
                && 'Test Data'[Status] = "5. Closing"
        )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Test Data'[Versions.properties.Created.Element:Text] ),
        FILTER (
            'Test Data',
            'Test Data'[Items.Id] = EARLIER ( 'Test Data'[Items.Id] )
        )
    )
VAR _days =
    CALCULATE (
        SUM ( 'Test Data'[DaysInSinceStart] ),
        FILTER (
            'Test Data',
            'Test Data'[Items.Id] = EARLIER ( 'Test Data'[Items.Id] )
        )
    )
RETURN
    IF (
        ISBLANK ( _closingcount ),
        _days + DATEDIFF ( _maxdate, TODAY(), DAY ),
        _days
    )

Best Regards

[Emoes]

Hello Rena,

I looked again at your solution and it is actually looking very good!

I'm only missing (in my source data) the actual closing date. (So this PBIX file is showing me the current (let's call it OPEN) status because the days will continue to increase along it has not been closed.

The closed date is not in my data set and needs to be added, so I can calculate the total time the process took between start and finish.

If I add that closing date (Finish) How can I then calculate this?

The end result should show per status the time in days (Check, this is there)

The total time it took when between start (now missing status"Closed") and finish.

Emoes

[v-yiruan-msft]

Hi @Emoes ,

I'm not very clear about your requirement... Could you please provide some fake data include actual closing data with Text format and your expected result with backend logic and special examples screenshots. Thank you.

Best Regards

[Emoes]

Hello,

Are you still able (and willing) to help me out?

Kind regards, Emoes

[Emoes]

Hello yingyinr,

How are you doing? Was my last explanation clear enough?

Thanks for your help.

Emoes

[Emoes]

Hello yingyinr,

I have created a new PBIX File (In Dropbox, I’m unable to connect the PBIX (or other attachments to this post)

I’m expecting the following result: (With accurate values) 

The start date of an Item is the first registered date.
(The status for that is not important, because it will not always start at “1. Requesting”)

Let’s take 3 items as an example: (Item “L”, “A”, “P”) (Manually calculate) This should be the result

Kind Regards, Emoes