Forum Discussion

atomek1000's avatar
atomek1000
Icon for Advocate I rankAdvocate I
9 years ago
Solved

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

4 Replies

    • atomek1000's avatar
      atomek1000
      Icon for Advocate I rankAdvocate I
      9 years ago

      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?