Agg after recent date

[Anonymous]

Hi everyone,

I have this table below that is connect to a Calendar Table.

Date	ID	Column1	Column2
31/07/2021	A	160	1060
31/08/2021	A	200	1170
30/09/2021	A	175	1300
31/07/2021	B	10	150
31/08/2021	B	12	200
30/09/2021	B	14	250

I want to calculate two metrics. They have to show the most recent result, that is defined by a slicer based on the Calendar Table. The metrics are:

• Total amount of column1
• Total amount of column2

I am struggling with getting the most recent result. Let's say that my slicer goes from 1/Jun/2021 to 15/Sep/2021 I want a card that shows me:

• Col1_A + Col1_B = 200+12 = 212
• Col2_A + Col2_B = 1170+200 = 1370

Any ideas on how to lock the most recent available value and then sum per ID? Thanks

[tamerj1 · Accepted solution]

Hi @Anonymous 

Please try

Card1 =
SUMX (
VALUES ( 'Table'[ID] ),
MAXX ( CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[Date] ) ), 'Table'[Column1] )
)

[Anonymous]

@tamerj1 thanks, it worked perfectly!