Get Value assigned to last entry each month

[Marius]

Hi,

I'm fairly new to Power BI and probably this is much easier than I make it. But I just can't get my brain to work through the problem.

I have a Table:

ID	Date	Value
1	01/01/2021	$1000
2	01/01/2021	$1100
3	01/01/2021	$1500
4	02/01/2021	$2800
5	02/01/2021	$2500
6	15/02/2021	$1500
7	15/02/2021	$1400
8	18/02/2021	$1700
9	27/02/2021	$1000

Now I want to show in a Visual always the value for the last row of each day and in another for each month or both together in a matrix.

The list is sorted before the import. So the highest ID of each Day/Month is also automatically the last entry. Maybe this helps.

I tried taking the "MAX ID" and put it into a matrix. But then I don't know how to obtain the related value to this MAX ID.

As I said, probably it is very easy and I just can't see it.

[stevedep · Accepted solution]

Hi,

Its possible to do it like this:

LastVal = 
CALCULATE(SUM('Table'[Value]), LASTNONBLANK(DimDate[Date].[Date], CALCULATE(SUM('Table'[Value]))))

As seen here:

File is attached.

Kind regards, Steve. 

[stevedep · Accepted solution]

Hi,

Its possible to do it like this:

LastVal = 
CALCULATE(SUM('Table'[Value]), LASTNONBLANK(DimDate[Date].[Date], CALCULATE(SUM('Table'[Value]))))

As seen here:

File is attached.

Kind regards, Steve. 

[Marius · Accepted solution]

Thanks, did not worked out for me on first try. Something is adding up to much. But I need to serach for the error in my Data model I guess. For now, with the hind of using "lookupvalue" I was able to solve the problem very easily as well.

LookupTest = LOOKUPVALUE('FACT - Table'[Value],'FACT - Table'[Value ID], MAX('FACT - Table'[Value ID]))

[rfigtree]

hopefully i didnt make a mistake in the syntax but here you go.

lastForDay= 
var _ID=calculate(max(table[id]),filter(all(table),table[date]=earlier(table[date])) 
return lookupvalue(table[value],table[id],_ID)

lastForDayYear= 
var _ID=calculate(max(table[id]),filter(all(table),Year(table[date])=Year(earlier(table[date]))) 
return lookupvalue(table[value],table[id],_ID)

[Marius]

Hi,

thanks, but I get an error on this.

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

My code with real table,colum names:

LastEntry = var _ID= CALCULATE(max('FACT - Saldo'[Saldo ID]), FILTER(ALL('FACT - Saldo'), 'FACT - Saldo'[Date] =EARLIER('FACT - Saldo'[Date])))
return LOOKUPVALUE('FACT - Saldo'[SALDO],'FACT - Saldo'[Saldo ID], _ID)

When I come to the "Earlier" part, it already does not show the dropdown to select my column. I just wrote it in by hand.

I don't understand the earlier funtion either. The Help article of it is complex as well...