Show only latest month data value

[nisuomi]

Hi,

There is a lot of asks for almost with the same header, but I could not find the solution to my problem.

So I have data and I want to only show the sum of things for the latest month only. So if I create a table, insert yearmonth value and then the measure, it should only show the measure value for the latest month that it can find from the data - nothing else.

So I have tried this:

Last Balance =
VAR endDate1 = MAXX(Table1;Table1[vuosikk])
return
CALCULATE(
SUM(Table1[peruslaskukaava]);
Table1[vuosikk] = endDate1
)

but only gets me to the way, where it shows the latest month value only for the year level per each..

And does not hide value for other months and show only for the latest.

This is what I have:

vuosi vuosikk Last Balance

2017 201701 380
2017 201711 -2000
2017 201712 400
2018 201801 380
2018 201802 -370
2018 201803 390

when it should be

vuosi vuosikk Last Balance

2017 201701 
2017 201711 
2017 201712 
2018 201801 
2018 201802 
2018 201803 390

Can't get my head around this, should not be so hard..

Cheers,

Niko

[PattemManohar]

@nisuomi Please try this...

Test39Out = 
VAR _MaxDate = MAX(Test39[YearMonth])
VAR _Result = SELECTCOLUMNS(Test39,"Year",Test39[Year],"YearMonth",[YearMonth],"LastBalance",IF(Test39[YearMonth]=_MaxDate,LOOKUPVALUE(Test39[Balance],Test39[YearMonth],_MaxDate),BLANK()))
RETURN _Result

[nisuomi]

Hello @PattemManohar

I guess I did not make clear enough start post, so my intention is not to create a separate table out of the current table.

But to create a measure. But is this even possible?

Nice tip tho, maybe I can get it working with that, if there is no resolution for the measure way.

Cheers,

Niko

[nisuomi · Accepted solution]

Hi @PattemManohar,

With this 

Last Balance =
VAR _MaxDate = MAXX(ALL(Table1[vuosikk]);Table1[vuosikk])
VAR _Result = IF(FIRSTNONBLANK(Table1[vuosikk];1)=_MaxDate ; sum(Table1[peruslaskukaava]);BLANK())
RETURN _Result

I got it working, that it shows only for the latest yearmonth value. But in this option, I have to click from the table "rows" dimension "show items with no value" to show it the way I want it to be seen.

vuosi vuosikk Last Balance
2018 201803 390

vuosi vuosikk Last Balance
2017 201701 (Blank)
2017 201711 (Blank)
2017 201712 (Blank)

2018 201801 (Blank)

2018 201802 (Blank)

2018 201803 390

Thanks for your input tho'! It got my brains rolling and was helpful for me 🙂 If you have any other solution, please, share!

Cheers,
Niko

[PattemManohar]

@nisuomi Great !!