Calculate time (days) status per item even if status goes back to previous used status.

[Emoes]

Hello, community,

I hope I can get some help on a topic I’m already struggling with for a long time.

I already had some help, but the result is not what I would expect.

My question is around the time a certain item was in a certain status.

I have items in an overview that come in the list in a certain status and move status FORWARD but also BACK.

I would like to know two things.

1. Total Time the item is in the list is working (see column TotalDays)
   This is using the following code:

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

• I would like to know How long a has been (or IS) in a certain status.

If you look at the example below, “Item A” in column “DaysInSinceStart” should all be one cell higher and the last cell should contain the time between June 9th and the today date (base on the last Column this should be September 12th)

I would expect the following  result (Using September 12th as TODAY)

So I can report for ITEM A, on the days that it was in a certain status.

 The used (not providing what is expect code is:)

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
    )

Can anyone please help me get this right calculated?

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

Thanks,

Emoes

[tamerj1 · Accepted solution]

Hi @Emoes 
Please use

DaysInSinceStart =
VAR CurrentDate = 'Test Data'[Versions.properties.Created.Element:Text]
VAR CurrentItemTable =
    CALCULATETABLE (
        'Test Data',
        ALLEXCEPT ( 'Test Data', 'Test Data'[Items.Title] )
    )
VAR TableAfter =
    FILTER (
        CurrentItemTable,
        'Test Data'[Versions.properties.Created.Element:Text] > CurrentDate
    )
VAR NextDate =
    COALESCE (
        MINX ( TableAfter, 'Test Data'[Versions.properties.Created.Element:Text] ),
        TODAY ()
    )
RETURN
    DATEDIFF ( CurrentDate, NextDate, DAY )

[Emoes]

@tamerj1 

Wow, thanks for your help, this is exactly what I needed.

Thanks for your quick reply and support.

Emoes.

[tamerj1 · Accepted solution]

Hi @Emoes 
Please use

DaysInSinceStart =
VAR CurrentDate = 'Test Data'[Versions.properties.Created.Element:Text]
VAR CurrentItemTable =
    CALCULATETABLE (
        'Test Data',
        ALLEXCEPT ( 'Test Data', 'Test Data'[Items.Title] )
    )
VAR TableAfter =
    FILTER (
        CurrentItemTable,
        'Test Data'[Versions.properties.Created.Element:Text] > CurrentDate
    )
VAR NextDate =
    COALESCE (
        MINX ( TableAfter, 'Test Data'[Versions.properties.Created.Element:Text] ),
        TODAY ()
    )
RETURN
    DATEDIFF ( CurrentDate, NextDate, DAY )

[Emoes]

Hello @tamerj1,

Thanks again for your help on my STATUS question

Can I (may I) I ask you 2 related questions, something I didn’t think of?

1. I see now that the TotalDays went up 
   In the example below 278 days, but from May 23rd till Today is only 113 days
   
   
2. I found out that, this was is caused by double counting. I have some Items that were changed twice on the same day and also calculated twice. Would it be possible to count the first one on that day, with a 0 (Zero)

Thanks, Emoes

[tamerj1 · Accepted solution]

Hi @Emoes 
The easiest way is to create a DateTime column as follows

DateTime = 'Test Data'[Versions.properties.Created.Element:Text.1] + 'Test Data'[Versions.properties.Created.Element:Text.2]

DaysInSinceStart = 
VAR CurrentDate = 'Test Data'[DateTime]
VAR CurrentItemTable =
    CALCULATETABLE (
        'Test Data',
        ALLEXCEPT ( 'Test Data', 'Test Data'[Items.Title] )
    )
VAR TableAfter =
    FILTER (
        CurrentItemTable,
        'Test Data'[DateTime] > CurrentDate
    )
VAR NextDate =
    COALESCE (
        MINX ( TableAfter, 'Test Data'[DateTime] ),
        TODAY ()
    )
RETURN
    DATEDIFF ( CurrentDate, NextDate, DAY )

[Emoes]

@tamerj1 ,

This is absolutely fabulous, great. And a big thanks.

I don’t want to place questions in question, but to add to the story.

My Source file has a Date and Time column. Format like: “2022-03-29T11:09:42” (without the “”)

When I placed my first request (and test file) I had transformed the Date/Time column only to a Date column. “2022-03-29”

When you asked me for the Time, I used the SPLIT function to split (on the “T”) the Date and Time to two columns (Date and Time)” 2022-03-29”  and “11:09:42”

Would it not be better not to transform the source data, and load the date/time column in its original format, and do the “calculation” on that column?  “2022-03-29T11:09:42”

Emoes

[tamerj1]

@Emoes 
Yes exactly. No need to perform any transformation just use the original column. However, the Date Only column might be required for other calculations. In this case you can create a new column using DATEVALUE function.

[tamerj1]

Hi @Emoes 
1. I don't see this example.

2. Do you have a time column?

[Emoes]

@tamerj1 

I have the time available.

I created a new test file (see link to dropbox, becuase I can't add files to this post, I don't kno why)

Emoes

[tamerj1]

@Emoes 

Sorry I left the office already.

This shall solve the problem of the ties. Still O did not understand the other problem 

DaysInSinceStart =
VAR CurrentTime = 'Test Data'[Time]
VAR CurrentDate = 'Test Data'[Versions.properties.Created.Element:Text]
VAR CurrentItemTable =
CALCULATETABLE (
'Test Data',
ALLEXCEPT ( 'Test Data', 'Test Data'[Items.Title] )
)
VAR TableAfter =
FILTER (
CurrentItemTable,
'Test Data'[Versions.properties.Created.Element:Text] > CurrentDate
&& 'Test Data'[Time] < CurrentTime
)
VAR NextDate =
COALESCE (
MINX ( TableAfter, 'Test Data'[Versions.properties.Created.Element:Text] ),
TODAY ()
)
RETURN
DATEDIFF ( CurrentDate, NextDate, DAY )

[Emoes]

@tamerj1 ,

No problem I appreciate your help.

But I tried the formule and the days are now more worse.

Let me also take a closer look at my Test data tommorow.

Emoes

[tamerj1]

@Emoes 

I'll have a look at it tomorrow as well. And you are right I think the time solution is not correct. I'll find something no worries.