drillthrough for YTD & Previous data

HI Anonymous ,

It looks like the drillthrough is correctly filtering by product but not by the specific fiscal year you clicked on. This happens because drillthrough in Power BI works primarily based on field values, and your table's columns (FY22, FY23, FY24) are likely measure-based rather than field-based.

 

Solution Approach:
Since your fiscal year columns (FY22, FY23, FY24) are measures, Power BI does not pass the column header (fiscal year) as a filter in the drillthrough action. To resolve this, follow these steps:

 

Step 1: Create a Drillthrough Page with a Year Filter
Create a new page and enable Drillthrough by dragging the FiscalYear column (from your Date table or fact table) into the Drillthrough Filters pane.

Add a table or any other visualization to display detailed data filtered by Product and FiscalYear.

 

Step 2: Modify Your Table to Support Drillthrough by Year
Since your visual is showing fiscal years as columns (which are measures), we need to unpivot the data model approach to allow Power BI to recognize the year being clicked.

Option 1: Change Table Structure
If possible, reshape your data model to have FiscalYear as a field instead of separate measures.

Your dataset should look like:

Product	FiscalYear	TotalBookedSalesAmt
AAH	FY22	85,444
AAH	FY23	57,603
AAH	FY24	41,196
ATS	FY22	12,924

Then, create a matrix visual with FiscalYear in Columns instead of separate measures.

 

Option 2: Using Selected Value for Drillthrough
If restructuring is not an option, you can create a measure to capture the selected year from the matrix:

DAX

SelectedFiscalYear =
SWITCH(
TRUE(),
ISINSCOPE('DrillThrough'[FY22]), "FY22",
ISINSCOPE('DrillThrough'[FY23]), "FY23",
ISINSCOPE('DrillThrough'[FY24]), "FY24"
)

Now, add this measure to your drillthrough page as a filter, and use it to filter the detailed table.

 

Step 3: Ensure Correct Filtering in Drillthrough Page
Modify your YTD Measures to use the selected fiscal year from the drillthrough filter:

DAX

TotalBookedSalesAmt_YTD_Drillthrough =
VAR SelectedYear = SELECTEDVALUE('DrillThrough'[FiscalYear]) -- Get selected fiscal year
VAR YearStart = DATE( VALUE(SelectedYear) - IF(MONTH(TODAY()) < 4, 1, 0), 4, 1)
RETURN
CALCULATE(
SUM(DrillThrough[TotalBookedSalesAmt]),
FILTER(
'DrillThrough',
'DrillThrough'[ActivityDate] >= YearStart &&
'DrillThrough'[ActivityDate] <= TODAY()
)
)/1000

Expected Outcome:
Clicking on AAH in FY23 will now drill through to a detail page that filters data for AAH and FY23 only.

The drillthrough page will only show the relevant year's data, rather than all years.

 

Please mark this post as solution if it helps you. Appreciate Kudos.

Hi Anonymous

 

Drillthrough doesn’t work well when you’re using separate columns for FY22, FY23, etc., because Power BI doesn’t pass the column header (fiscal year) to the drillthrough filter. It only passes row-level fields like product.

 

To fix this, you can either unpivot your table so fiscal year becomes a row field (which is better for visuals and drillthrough), or if you want to keep the current layout, use a measure like this to detect the selected year:

 

SelectedFiscalYear =
SELECTEDVALUE('YourMatrixTable'[FiscalYear])

 

Then use that to filter your drillthrough page using a measure or calculated column. It’ll let you properly drill into the selected year’s data.