Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nisuomi
Resolver I
Resolver I

Show only latest month data value

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

1 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

View solution in original post

4 REPLIES 4
PattemManohar
Community Champion
Community Champion

@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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




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

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

@nisuomi Great !!





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.