cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Partisan

## Days spent in stage

Hi sir/mam,

I have below data and I want to generate 'Days spent on the Oldstage' column in power bi. I have also given referance column to know how I calculated this column in excel. Basically I want to know how many days were spent at a stage befor moving to a new stage. I want to follow the sequence of the stages as A>B>C>D.

 Id Movement start Date OldStage NewStage Days spent on the Oldstage Formula for referance 1 28-10-20 15:32 B C 7.587199074 C date - B date 1 28-10-20 15:29 A B 0.001886574 B date - A date 1 05-11-20 5:38 C D 3 D date - C date 1 08-11-20 5:38 D E NA NA 2 28-11-20 15:29 A B 1 B date - A date 2 29-11-20 15:29 B C 10 C date - B date 2 09-12-20 15:29 C D 2 D date - C date 2 11-12-20 15:29 D E NA NA
4 REPLIES 4
Super User

Create the following calculated column in your table:?

``````Days Spent =

VAR __FromStage = Stages[OldStage]
VAR __TOStage = Stages[NewStage]
VAR __FromTime =
CALCULATE(
MAX( Stages[Movement start Date] ) ,
Stages[OldStage] = __TOStage,
ALLEXCEPT( Stages , Stages[Id] )
)
VAR __Result=
DATEDIFF( Stages[Movement start Date] , __FromTime , DAY )
RETURN
__Result
``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

For row 2, 8-10-20 15:29 + 0.001886574 = 28-10-2020 15:31 but this  datetime is not anywhere in your sample data.  And is this by Id?

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Post Partisan

Thanks @danextian for looking into this.

I have this datetime, its actually 28-10-20 15:32.Ignore the last decimaldue to rounding off..

It is the start day/time of Stage B. When I compare this date with start day/time of Stage A (28-10-20 15:29), I get value as 0.001886574.

Super User
``````Days spent =
CALCULATE (
MAX ( 'Table'[Movement start Date] ),
FILTER (
'Table',
'Table'[OldStage] = EARLIER ( 'Table'[NewStage] ) && 'Table'[Id] = EARLIER( 'Table'[Id])
)
) - 'Table'[Movement start Date]
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors