topic Re: for the maximum timestamp per day per case ID and per assignment group in DAX Commands and Tips

for the maximum timestamp per day per case ID and per assignment group

Anonymous — Thu, 25 Jun 2020 12:18:29 GMT

This is a sample below. Based on each ID and assignment group per day, I would like to sum the workload that has the maximum/latest timestamp.

ID	Workload	Timestamp	Assignment group	Activity
1	1	21/01/2020 21:01:21	abcd	Open
1	-1	21/01/2020 22:01:21	abcd	reassigned
2	1	21/01/2020 9:00:00	aruv	open
1	0	22/01/2020 9:00:00	abce	in progress
1	1	22/01/2020 9:00:00	abce	assigned

Re: for the maximum timestamp per day per case ID and per assignment group

Anonymous — Thu, 25 Jun 2020 11:56:41 GMT

Hi Anonymous ,

Try this measure

SumWorkload =

Var filtertable = CALCULATE(MAX('Table'[Timestamp]),FILTER (ALL('Table'),'Table'[ID] = MAX('Table'[ID]) && 'Table'[Assignment group] = MAx('Table'[Assignment group])))

RETURN

CALCULATE(sum('Table'[Workload]),FILTER('Table','Table'[Timestamp] = filtertable))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: for the maximum timestamp per day per case ID and per assignment group

Anonymous — Thu, 25 Jun 2020 12:19:12 GMT

Hi Harsh,

This solution gives me a blank. I have edited my question.

Re: for the maximum timestamp per day per case ID and per assignment group

Anonymous — Thu, 25 Jun 2020 12:24:39 GMT

Hi Anonymous ,

I am getting this.

Can you share the expected output.

Regards,

Harsh Nathani

Re: for the maximum timestamp per day per case ID and per assignment group

Anonymous — Thu, 25 Jun 2020 12:36:59 GMT

ID	Workload	Timestamp	Assignment	Activity	SumWorkload
1	1	21/01/2020 21:01:21	abcd	Open	-1
1	-1	21/01/2020 22:01:21	abcd	Reassigned	-1
2	1	22/01/2020 9:00:00	aruv	Open	2
1	0	22/01/2020 9:00:00	abce	In Progress	2
1	1	22/01/2020 9:00:00	abce	Assigned	2

Re: for the maximum timestamp per day per case ID and per assignment group

Anonymous — Thu, 25 Jun 2020 13:01:55 GMT

Anonymous ,

Not clear with the logic.

How is Id 2 2 when you are adding workload.

Regrds,

Re: for the maximum timestamp per day per case ID and per assignment group

Anonymous — Thu, 25 Jun 2020 13:06:26 GMT

On day 22/01/2020 ID 1 has a workload contribution of 1, ID 2 has a workload contribution of 1, so in total the workload is 2. I want that to be reflected.

Re: for the maximum timestamp per day per case ID and per assignment group

amitchandak — Thu, 25 Jun 2020 17:29:06 GMT

Anonymous , Try like

sumx(Summarize(Table,Table[Assignment],"_1", lastnonblankvalue(Table[Timestamp],sum(Table[Workload]))),[_1])