Question

How can I dynamically filter Top N customers based on sales in the most recent fiscal quarter using a time slicer?
I want to filter the Top N customers based on sales in the most recent fiscal quarter using a time slicer in Power BI. I need to understand how to create a dynamic filter that updates automatically as the time slicer changes, showing only the top-performing customers for the selected quarter.

Answer 1

To dynamically filter Top N customers based on sales in the most recent fiscal quarter using a time slicer:

1. Create a Date Table with Fiscal Year and Fiscal Quarter columns.

2. Create a Total Sales measure:

TotalSales = SUM('Sales'[Amount])

Create a measure for sales in the most recent fiscal quarter:

SalesInMostRecentQuarter = 
VAR MaxFiscalQuarter = CALCULATE(MAX('DateTable'[FiscalQuarter]), ALL('DateTable'))
VAR MaxFiscalYear = CALCULATE(MAX('DateTable'[FiscalYear]), ALL('DateTable'))
RETURN CALCULATE([TotalSales], 'DateTable'[FiscalYear] = MaxFiscalYear, 'DateTable'[FiscalQuarter] = MaxFiscalQuarter)

Create a rank measure for customers:

CustomerRank = 
RANKX(
    FILTER(ALL('Sales'[CustomerID]), NOT ISBLANK([SalesInMostRecentQuarter])),
    [SalesInMostRecentQuarter],
    , DESC, Dense
)

Add the Rank measure to a visual's filter and set it to Top N based on SalesInMostRecentQuarter.

Now, when using the time slicer, the Top N customers will update automatically based on sales in the most recent fiscal quarter.