TOPN by year-month but showing all dates in matrix

[Jonas_Simon]

Hello everybody.

My problem is the following: The client asked to see in a matrix the fluctuation of the unit value of the last 13 months of the top 15 items by the total value of the month and year that he chooses in the slicer (slicer contains data from d_Calendar).

So, in the matrix:
- the top15 items are on the lines;
- the year and month in the columns;
- the unit value of each year-month in the values.

The idea is that, when he chooses a year-month in the slicer (August 2022 for example), the matrix will show the top15 items by total value for that month, but with unit values for the last 13 months (considering the current month).

I was able to do the TOPN without any problems:

TOPN unit value =
CALCULATE (
[Vl. Unit],
KEEPFILTERS ( TOPN ( 15, ALL ( d_products[Items] ), [Total Value], DESC ) )
)

What I'm not getting is to make the matrix only have the 15 items of the selected month and showing all 13 months, that is, the date filter has to be applied to the items, but not the columns and values of the matrix.

Model:
- A fact table where the unit value is calculated, which is the division of the total value by the total volume;
- A calendar where the dates come from (Date to Fact[Date], 1 to *);
- A products table where the items come from (Prod_ID to Fact[Prod_ID, 1 to *).

Measurements =
Total Value = SUM( Fact_table[Total] )
Total Volume = SUM( Fact_table[Qty] )
Vl. Unit = DIVIDE( [Total Value], [Total Volume] )

Phases:
- Apply the date filter on the slicer (August 2022);
- In the matrix is the TOP 15 items by total value of the month and year selected (15 lines only);
- In the columns the last 13 months considering the current month;
- The unit values for each month.

Thanks for the help.

[Jonas_Simon · Accepted solution]

I managed to make it work in another way, but using the idea of a separate table.

The measure was created as follows:

TOPN unit value =

VAR _Table = TOPN( 15, ALL ( d_products[Items] ), [Total Value], DESC )

RETURN

CALCULATE(
CALCULATE( [Vl Unit], USERELATIONSHIP( Calendar2[Date], Fact[Date] ), REMOVEFILTERS( Calendar ) ),
FILTER( d_products, d_products[Items] in _Table )
)

The idea is to remove the filter that comes from Calendar and use the inactive relationship from Calendar2 to bring the values into the array, and use the _Table virtual table to filter the items based on TOPN.

In the matrix I used the year and month fields from calendar2 and in the slicer I used the year and month fields from calendar.

[Jonas_Simon]

First, thanks for the reply 🙂

So, I created a second table of dates, it worked, it correctly filters the 15 items of each month, but the unit values are repeated in the matrix, it is not allocating the correct value of each month, I tried using the fields of calendar and calendar2 both in the columns of the matrix and in the filter slicer.

Does this second table need to be related to the fact table? The year and month fields in the array, do I use the first or second date table? What about the slicer?

My apologies for the delay in responding.

[amitchandak]

@Jonas_Simon , If you want to show data of 13 month based selected/ date period the date should come from an independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1

var _min2 = eomonth(_max, -1) +1

var _me =CALCULATE (
[Vl. Unit],
KEEPFILTERS ( TOPN ( 15, ALL ( d_products[Items] ), calculate([Total Value], filter('Date', 'Date'[Date] >=_min2 && 'Date'[Date] <=_max)), DESC ) )
)
return
calculate( _me, filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE