DAX: calculate values based on last date by group

[Anonymous]

Hi guys, can you help me with this? Im having issues calculating the last date by group of data

Let's say I have this table:

Region	Bank	Type	Date	Value
NA	Bank1	A	1/1/2017	90
NA	Bank1	A	1/12/2017	30
NA	Bank1	B	3/4/2017	40
NA	Bank1	B	2/2/2018	20
NA	Bank2	A	1/1/2017	50
NA	Bank2	A	1/4/2017	60
SA	Bank1	B	1/1/2017	90
SA	Bank1	B	4/1/2017	20
SA	Bank1	B	4/16/2017	10
SA	Bank1	C	1/1/2017	60
SA	Bank3	A	1/1/2017	50
SA	Bank3	A	3/15/2017	70
EU	Bank4	D	1/1/2017	40
EU	Bank4	D	1/1/2018	0

Basically want I'm trying to get is the value by lastest date by group. So my logic was:

1. Create new measure for LastDate

2. Create new measure to filter data only if it is last date

The idea is to finally have something like this:

Region	Bank	Type	Date	Value	LastDate	LastValue
NA	Bank1	A	1/1/2017	90	1/12/2017
NA	Bank1	A	1/12/2017	30	1/12/2017	30
NA	Bank1	B	3/4/2017	40	2/2/2018
NA	Bank1	B	2/2/2018	20	2/2/2018	20
NA	Bank2	A	1/1/2017	50	1/4/2017
NA	Bank2	A	1/4/2017	60	1/4/2017	60
SA	Bank1	B	1/1/2017	90	4/16/2017
SA	Bank1	B	4/1/2017	20	4/16/2017
SA	Bank1	B	4/16/2017	10	4/16/2017	10
SA	Bank1	C	1/1/2017	60	1/1/2017	60
SA	Bank3	A	1/1/2017	50	3/15/2017
SA	Bank3	A	3/15/2017	70	3/15/2017	70
EU	Bank4	D	1/1/2017	40	1/1/2018
EU	Bank4	D	1/1/2018	0	1/1/2018	0

In order to show this:

NA	Bank1	A	1/12/2017	30	1/12/2017	30
NA	Bank1	B	2/2/2018	20	2/2/2018	20
NA	Bank2	A	1/4/2017	60	1/4/2017	60
SA	Bank1	B	4/16/2017	10	4/16/2017	10
SA	Bank1	C	1/1/2017	60	1/1/2017	60
SA	Bank3	A	3/15/2017	70	3/15/2017	70
EU	Bank4	D	1/1/2018	0	1/1/2018	0

Is the logic ok? Can you help me to calculate LastData and LastValue measures? Thanks!

[PattemManohar · Accepted solution]

@Anonymous I guess you are looking for something like this...

Step 1 : Create an Index field in "Power Query Editor"

Step 2 : Create a Rnk field as below

Rnk = RANKX(FILTER(Test128LastVal,Test128LastVal[Region]=EARLIER(Test128LastVal[Region]) && Test128LastVal[Bank] = EARLIER(Test128LastVal[Bank]) && Test128LastVal[Type] = EARLIER(Test128LastVal[Type])),Test128LastVal[Index],,ASC,Dense)

Step 3 : Create an another RnkCheck as below

RnkCheck = CALCULATE(MAX(Test128LastVal[Rnk]),FILTER(ALL(Test128LastVal),Test128LastVal[Region]=EARLIER(Test128LastVal[Region]) && Test128LastVal[Bank] = EARLIER(Test128LastVal[Bank]) && Test128LastVal[Type] = EARLIER(Test128LastVal[Type])))

Step 4 : Finally, create a new table as below

Test128Out = FILTER(Test128LastVal,Test128LastVal[Rnk]=Test128LastVal[RnkCheck])

[Anonymous]

use a measure and a Calendar Table 

First Make a new table 

Calendar= CALENDAR(MIN(Table[Date),Max(Table[Date])

Create a relationships and then make the measure 

LastValue=Calculate(Sum(Value),LastDate(calendar'Dates) ) 

[parry2k]

@Anonymous wht you mean last date/value by group? What is group?

[Anonymous]

Yeah title is wrong, I mean last date by group. And group = set of data with same Region/Bank/Type