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

[Anonymous]

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

[amitchandak]

@Anonymous , Try like

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

[harshnathani]

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)

[Anonymous]

Hi Harsh,

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

[harshnathani]

Hi @Anonymous ,

I am getting this.

Can you share the expected output.

Regards,

Harsh Nathani

[Anonymous]

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

[harshnathani]

@Anonymous ,

Not clear with the logic.

How is Id 2 2 when you are adding workload.

Regrds,

HN

[Anonymous]

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.