Creating a Summarized Table by Top N

[Anonymous]

Hi,

I'm trying to create a summarized table filtered on the top (most recent) 12 months in the original table.  It would need to be filtered/summarized by the "year_period" field which is in a format like "202111" shown below.

Any help accomplishing this would be much appreciated.  Thanks!

Jordan

[amitchandak]

@Anonymous ,

Create a new column in the table

Year month = format([Date], "YYYYMM")

Create a new table

summarize(Table, Table[Month Year], "Measure", sum(Table[Value] ) )

Add other per need

[Anonymous]

Thanks @amitchandak  but I'm not sure how this would summarize the new table by Top 12 months in original table?

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I create a sample to show you how to create a summarized TopN month table by Dax.

Try this code to create a calcualted table.

Summarized Table by Top N =
VAR _N = 12
VAR _SUMMARIZED =
    SUMMARIZE ( 'Table', 'Table'[year_period] )
VAR _ADDRANK =
    ADDCOLUMNS ( _SUMMARIZED, "RANK", RANKX ( _SUMMARIZED, [year_period],, DESC ) )
VAR _TopN =
    SUMMARIZE ( FILTER ( _ADDRANK, [RANK] <= _N ), [year_period] )
RETURN
    _TopN

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.