topic Re: Row based time calculation in DAX Commands and Tips

Row based time calculation

Danyal — Wed, 26 Jul 2023 11:39:38 GMT

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

Re: Row based time calculation

SamInogic — Tue, 01 Aug 2023 07:32:13 GMT

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/

Re: Row based time calculation

Danyal — Tue, 01 Aug 2023 10:25:19 GMT

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.

😊