Need Help : Data Grouping by Duration and Process

[BarathM]

Hi

    I need some help. I am newbie to Power Bi. I have data table as shown below.  I would like to add a new column to calculate the duration differents between "End Date" - "Start Date" in days.

1. Then I would like to group the the data by durations in day. This is for 1st scenario. For example,

< 0 day 

> 1 day and < 2 day

> 2 day and < 3 day

2. I also would like to do grouping like how many times  units when thru how many Process steps. This is the second scenario.

Unit Id	Start Date	End Date	Process
1	8/4/2020 23:59	8/5/2020 17:21	BT
2	8/4/2020 13:49	8/4/2020 18:05	BT
3	8/3/2020 21:24	8/4/2020 8:14	BT
4	8/3/2020 16:13	8/4/2020 2:28	BT
5	8/3/2020 12:27	8/4/2020 8:07	BT
2	8/3/2020 10:19	8/3/2020 11:13	Proscan
3	8/3/2020 7:45	8/4/2020 2:11	Proscan
1	8/2/2020 19:49	8/3/2020 18:43	Proscan
1	8/2/2020 9:18	8/3/2020 11:27	FT
1	8/2/2020 9:05	8/3/2020 11:06	BT
5	8/1/2020 14:10	8/3/2020 11:27	Proscan

Thank you

[v-juanli-msft]

Hi @BarathM 

1. 

create calculated columns

duration = DATEDIFF([Start Date],[End Date],DAY) 

groupbyduration =
SWITCH (
    TRUE (),
    [duration] <= 0, "<=0 day",
    [duration] >= 1
        && [duration] < 2, ">=1 and <2 day",
    [duration] >= 2
        && [duration] < 3, ">=2 and  day"
)

2.

"do grouping like how many times  units when thru how many Process steps",

what does this mean?

does it group by units or process steps?

count time diff or different units?

If you need to count units grouped by process, create a measure like this

Measure = CALCULATE(COUNT('Table 3'[Unit Id]),ALLEXCEPT('Table 3','Table 3'[Process]))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@BarathM - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[amitchandak]

@BarathM , refer SEGMENTATION can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization