Your input on how to approach flow/step time analysis

[cplesner]

Hi there

I'm not sure what to call this, but I would like your input on how you would approach this need for more dynamic approach to analysing "flow durations", meaning that the end user will have the flexibility to decide on which flow steps to include/exclude in their analysis.

Ok, so let's say that i have the following input table :

Now I want to be able to calculate durations between selected markers as an end user. In other words, I can't just make a calculated column that does a datediff between current and previous row for each person.

In my report I would like to be able to do the following steps as part of my anaysis of looking at the average duration of selected flows for example. 

So without any filters, I would start of with the following. Once i deselect certain "Marker", the Flow_1-Flow_x and Flow Durations will adjust.

Now as I see it, the adjustment could either be done by having one filter on "Markers" or alternatively have a filter for each Flow X option, so that I ex. for Flow2 selects "B0->F2" and for Flow 3 select "F2->E1" etc.

How would you approach this and/or are there a general pattern for this that I could use ? Size wise there is thousands of persons in the dataset and houndreds of markers.

[cplesner]

Thanks @amitchandak 

I've looked into that one and got it partly working as pr below

Now my challenge is to somehow "Pivot" the rows into Columns Attributes (Flow1,Duration1,Flow2,etc.). I have the Ranking but as soon as I remove Time and Marker from the Table, the calculations are gone. So I need somehow to freeze the "table" and then have Flow1,Flow2,Flow3 attributes refer to that - or am I completely of ?

Sample Workbook is here: flow step analysis.pbix

[amitchandak]

@cplesner , you can use offset function. But if you want based on marker, then you need a sort column too

Offset Previous Row = CALCULATE(Max('Table'[Time]) ,OFFSET(-1, ALL('Table'[Person],'Table'[Time]),ORDERBY('Table'[Time],asc),, PARTITIONBY('Table'[Person]) ))

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U