Filter on Latest Date

[PowerrrBrrr]

I have a table where I would like to check the average value on latest date but these average values are data points for different description,
so for example I have this table:

Table A

Id	Machines
1	MachineA
2	MachineA
3	MachineA
4	MachineA

Table B

ID	timeseriesId	Description
1	34333	Pressure rate
1	34339	Flow rate
1	44343	Temperature
2	22211	Pressure rate
2	33222	Flow rate

Table C

TimeseriesId	Value	Timestamp
34333	34	2021-12-12 12:09:12
34333	43	2021-12-21 10:00:00
34333	12	2021-01-10 21:09:12
44343	19	2021-12-12 12:09:12
22211	21	2021-12-10 12:09:12
22211	91	2021-11-11 12:09:12

So if you see in these three tables(all conneceted by one-many relationship) I am interested in getting the Pressure rate recorded latest for each machine. So in here I am interested to create a measure which gives me value (43) as that is the latest Pressure rate recorded for machineA and 21 which is latest pressure rate recorded for machineB. Similary I would like this for other machines. How can I do this??

[jppv20 · Accepted solution]

Hi @PowerrrBrrr ,

You can try this measure:

Pressure Rate = CALCULATE(SUM('Table C'[Value]),'Table B'[Description]="Pressure rate",FILTER('Table C','Table C'[Timestamp]=MAX('Table C'[Timestamp])))

If I answered your question, please mark it as a solution to help other members find it more quickly.

[jppv20 · Accepted solution]

Hi @PowerrrBrrr ,

You can try this measure:

Pressure Rate = CALCULATE(SUM('Table C'[Value]),'Table B'[Description]="Pressure rate",FILTER('Table C','Table C'[Timestamp]=MAX('Table C'[Timestamp])))

If I answered your question, please mark it as a solution to help other members find it more quickly.

[PowerrrBrrr · Accepted solution]

@jppv20 I dont get the result. I took a table visual and added ID timestamp and pressure rate, and I get blank . there is no values in any column

[Anonymous]

@PowerrrBrrr Try this:

Measure=var LastTime=LASTNONBLANK('Table C'[Timestamp],SUM('Table C'[Value]))

return CALCULATE(SUM('Table C'[Value]),FILTER('Table C','Table C'[Timestamp]=LastTime&&'Table B'[Description]="Pressure rate"))

[PowerrrBrrr]

Here tableB[Desciption ] gives an error in measure. it seems to be not available when creating measure