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
atomek1000
Advocate I
Advocate I

Measure for 2 last months

Hello, i am new to powerBi and i have a table which has column Month in string format like this

2016-05
2016-06
2016-07
2016-08

 

I want to have measure which always chooses 2 top values and put it like slicer for graph.

I tried TOPN method but without luck.

 

For this example measure should just show

2016-07
2016-08

 

Thanks in advance for help

2 ACCEPTED SOLUTIONS

@atomek1000

 

This maybe help you.

 

top2.png




Lima - Peru

View solution in original post

Hi @atomek1000,

Do you have resolved your issue, you can add a rank column using the following formulas. And always filter the table records using 1 and 2 order.

 

rank=RANKX(Table1,Table[month],DESC)


If you have any issue, please feel free to ask.

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
Vvelarde
Community Champion
Community Champion

@atomek1000

 

Create a new Table:

 

Table = TOPN(2,Table1,Table1[Month],DESC)




Lima - Peru

@VvelardeThanks, that's generally what i need but in my table there is a lot od records for each month. How do i sort them by top2 months? I triend SUMMARIZE but then i only get 1 record in MONTH column(cannot aggregate by more columns because each record is different).

 

So this query

Last2MonthsTable = TOPN(2,Summarize('Distribution per Rep per Customer','Distribution per Rep per Customer'[Month]) ,'Distribution per Rep per Customer'[Month],DESC)

 

Gives me only 1 record with month because there are lots of records with this month.

 

In table there are 2 additional columns which i would like to have to visualise graph.

So this TOPN(2) should give me lots of records with TOP2 months. As i then avg data in graph.

In mssql i would just use subquery on where clause.

 

Could you help me solve it?

 

@atomek1000

 

This maybe help you.

 

top2.png




Lima - Peru

Hi @atomek1000,

Do you have resolved your issue, you can add a rank column using the following formulas. And always filter the table records using 1 and 2 order.

 

rank=RANKX(Table1,Table[month],DESC)


If you have any issue, please feel free to ask.

Best Regards,
Angelia

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.