topic Re: Calculate time (days) status per item even if status goes back to previous used status. in DAX Commands and Tips

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

Anonymous — Tue, 13 Sep 2022 10:07:27 GMT

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.

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

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

tamerj1 — Tue, 13 Sep 2022 10:33:04 GMT

Hi Anonymous
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 )

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

Anonymous — Tue, 13 Sep 2022 10:57:36 GMT

tamerj1

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

Thanks for your quick reply and support.

Emoes.

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

Anonymous — Tue, 13 Sep 2022 12:51:42 GMT

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?

I see now that the TotalDays went up
In the example below 278 days, but from May 23rd till Today is only 113 days
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

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

tamerj1 — Tue, 13 Sep 2022 12:52:18 GMT

Hi Anonymous
1. I don't see this example.

2. Do you have a time column?

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

Anonymous — Tue, 13 Sep 2022 14:05:13 GMT

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

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

tamerj1 — Tue, 13 Sep 2022 14:16:20 GMT

Anonymous

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 )

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

Anonymous — Tue, 13 Sep 2022 14:23:28 GMT

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

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

tamerj1 — Tue, 13 Sep 2022 14:54:49 GMT

Anonymous

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.

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

tamerj1 — Wed, 14 Sep 2022 04:27:56 GMT

Hi Anonymous
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 )

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

Anonymous — Wed, 14 Sep 2022 06:47:29 GMT

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

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

tamerj1 — Wed, 14 Sep 2022 09:50:49 GMT

Anonymous
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.