Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Emoes
Helper I
Helper I

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

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

9 REPLIES 9
Emoes
Helper I
Helper I

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_
Super User
Super User

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

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


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. RequestingHow many days having this statusTotal 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 YETHow many days having this status
3. Gathering Detailed InformationHow many days having this status
4. Under ReviewHow many days having this status
5. ClosingLast date used for calculation other status
6. HibernatingHow 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:

StatusItems.IdItems.TitleVersions.properties.Created.Element:TekstDaysRemarkTotal Time in days
1. Requesting135C10-3-2022 00:000Start moment (First date found for Item.Title)0
1. Requesting135C22-3-2022 00:0012  
1. Requesting135C24-3-2022 00:002  
    5Total Days in "1. Requesting" = 12 + 2 +5 is 19 
4. Under Review135C29-3-2022 00:000  
4. Under Review135C14-4-2022 00:0016  
4. Under Review135C26-4-2022 00:0012  
5. Closing135C27-4-2022 00:001  
5. Closing135C10-5-2022 00:0013Total Days in "4. Under Review" = 16 + 12 +1 + 13  is 42 (Using tha last closing date61
       
1. Requesting136J10-3-2022 00:000 0
1. Requesting136J11-4-2022 00:0032  
1. Requesting136J14-4-2022 00:003  
1. Requesting136J12-5-2022 00:0028  
1. Requesting136J23-5-2022 00:0011  
  Today29-6-202237Total time in "1. Requesting" 32 + 3 + 28 + 11 + 37 = 111 days111

 

Kind Regards, Emoes

 

 

 

 

 

 

 

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
    )

yingyinr_0-1656666550616.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

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

Kind regards, Emoes

Hello yingyinr,

 

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

Thanks for your help.

 

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) Expected result.jpg

 

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

Expected result Correct data.jpg

 

Kind Regards, Emoes

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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