Index match, calculation with multiple option filter

[Anonymous2023]

Hi Team,

I am new to Power BI and so far has spent lots of time search relevent answer for my analysis.

I am always an excel users, but due to the size of my new datasets (>1GB), Power BI appear to be a better option.

Okay let me get this straight: I would like to calculate the duration of the activities taking place for each participants, but I only have data of specific milestones of each participants as rows. How should I proceeed?

My table

Participant	Milestone	Date
1011123	Start	1989/1/1
1011198	start1	1988/1/1
1011167	End	2000/2/1
1011123	end1	2002/3/1
1011198	end	2011/1/1
1011167	start	1989/1/1
1011123	pause	1998/1/1

My expected results 

Participants	Start time	End time	Duration
1011123	1989/1/1	2002/1/1	13 year
1011167	1989/1/1	2000/1/1	11 year
1011198	1988/1/1	2011/1/1	23 year

My thoughts (in excel logic):

1. Create additional column that converts "Start", "start" and "start1" as "start" (and for "end" as well)

2. Indexmatch the time for each participant in start time and end time columns.

3. Calculate the duration.

I was stopped at the first step, I can't really create a new table with filter that includes all the "start" related status. (OR and || didnt work unforutnately)

Happy to see how I could proceed, thank you.

[NaveenGandhi]

Hello @Anonymous2023 

Try the below dax to create a new table.


Milestone_Table =
ADDCOLUMNS (
SUMMARIZE (
'Table (2)',
'Table (2)'[Participant],
"Start_Time",
CALCULATE (
MIN ( 'Table (2)'[Date] ),
'Table (2)'[Milestone] = "start"
|| 'Table (2)'[Milestone] = "Start"
|| 'Table (2)'[Milestone] = "start1"
),
"End_Time",
CALCULATE (
MIN ( 'Table (2)'[Date] ),
'Table (2)'[Milestone] = "end"
|| 'Table (2)'[Milestone] = "End"
|| 'Table (2)'[Milestone] = "end1"
)
),
"Duration", DATEDIFF ( [Start_Time], [End_Time], YEAR )
)

Let me know if you have any questions.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate your kudos!!

[Anonymous2023]

Hi Thank you for your answer.
Just a quick follow-up.
In the original dataset, there is some milestone that is before the start date, which means that MIN does not apply. (I did not include that here for simplicity.)
How should I proceed with this situation?
Thank you.

[NaveenGandhi]

@Anonymous2023 

Is that milestone a different category? If yes you can keep aggregating them as seperate columns same as how i have created start time and end time in the above measure.

Regards,

Naveen