Row based time calculation

[Danyal]

Good Day everyone

I need a measure where i can show hours/mins 1 customer took from one to another move?

 

data_source_veiw

P_ID      Customer            Move_Name    Move_ID  change_time

9250	botana	New	3	7/24/2023 6:25
9250	botana	Hold	5	7/24/2023 6:29
9118	Gysa	New	3	7/23/2023 13:08
9118	Gysa	Approved	11	7/23/2023 13:08
9118	Gysa	Won	2	7/23/2023 13:11
9118	Gysa	Business	15	7/24/2023 7:03
9275	hana	New	13	7/24/2023 10:45
9275	hana	Hold	5	7/24/2023 10:46
9157	hhmaakka2	New	3	7/20/2023 6:50
9157	hhmaakka2	Approved	11	7/20/2023 9:25
9157	hhmaakka2	Won	2	7/20/2023 10:21
6748	iflash	New	3	7/24/2023 13:27
6748	iflash	Hold	5	7/24/2023 13:28
9116	coffee	New	3	7/23/2023 8:48
9116	coffee	Qualified	10	7/23/2023 8:48
9116	coffee	Won	2	7/23/2023 8:24
9116	coffee	Business	15	7/23/2023 8:23
9146	w2fgii	New	3	7/20/2023 7:17
9146	w2fgii	Hold	5	7/20/2023 7:23
9146	w2fgii	Qualified	10	7/20/2023 7:33
9146	w2fgii	Approved	11	7/20/2023 7:35

[Danyal]

HI @SamInogic ,

Thank you so much for helping out.

the provided measure seems fine but i doubt one thing, lets say one Move has 3 or more ids as one move will go under sevaral moves, so the goal is to calculate hours or minuts that a customer has spent in each move or from one to another move.

so the ultimate result would be i will create 2 slicers to select desied moves and it will show the hours or mintus that customer and move has taken.

thanks again for your input.

😊

[SamInogic]

Hi @Danyal ,

As per our understanding, you want to calculate the date difference between dates of “change_time” column as mentioned in the “data_source_veiw” table.

So, you can try to create a measure with below DAX expression,

diff =

    var _min=CALCULATE(MIN(data_source_veiw[change_time]),ALLEXCEPT('data_source_veiw',data_source_veiw[P_ID]))

    var _max=CALCULATE(MAX(data_source_veiw[change_time]),ALLEXCEPT('data_source_veiw',data_source_veiw[P_ID]))

return

    DATEDIFF(_min,_max,MINUTE)

Please refer to the below screenshot for the same,

Thanks!

Inogic Professional Services Division

Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

Drop an email at crm@inogic.com

Services:  http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/