Max per group using filter with DAX

[Anonymous]

Greetings, i have a table with 3 columns: date, machine and signal (there are lots of machines and signals and date is updated daily)

Date	Machine	Signal
25/03/2021	x	A
25/03/2021	x	B
25/03/2021	y	A
25/03/2021	y	B
25/03/2021	y	C
26/03/2021	x	C
26/03/2021	x	A
27/03/2021	x	A
27/03/2021	x	B
27/03/2021	y	C

What i need is to find out for an specific day how long it has been since i received signals from machines, for example for march 27 it would be:

	A	B	C
x	0	0	1
y	2	2	0

anf for march 26 it would be:

	A	B	C
x	0	1	0
y	1	1	1

I know this is max per group related, but could not came up with the right measure, any help would be appreciated.

Regards

[Vera_33]

Hi @Anonymous 

Do you have a Date table with relationship to this table? I have one, the slicer coming from date column from this Date table

test = 
VAR CurDate = SELECTEDVALUE('Date'[Date])
VAR LDate = COALESCE(MAXX(FILTER(yourTable,yourTable[Date]<=CurDate),yourTable[Date]),CurDate)
RETURN
DATEDIFF(LDate,CurDate,DAY)

[Anonymous · Accepted solution]

Hi @Vera_33 

Thanks for your answer, since i have lots of signals i have to use a matrix visual for the columns.

With your help i´ve managed to make a measure that returns exactly what i need, but i have to use two date slicers:

This is the measure i used:

MaxPC =

var fecfin = MAX(Fecha[Date])

var fecini = CALCULATE(MAX(table[date]),ALLEXCEPT(table,table[equip],table[signal],table[date]))

return

DATEDIFF(fecini,fecfin,DAY)