Ignore only Year Filter in Drillthrough

[y5famfnatudu]

Hello,

In a power bi report, I have a button that redirects me to a Drillthrough page, that contains the following 2 tables

Revenue values are calculated by measures (CY and PY), the rest are normal fields in tables.

The report also contains a year slicer, along with other slicers and search boxes.

What I would like to see on the Drillthrough page is:

When I click on row C, I want to view only the invoices from the Previous Year that are associated with the 250 (Revenue PY) measure.

Best regards,

Simon

[Anonymous]

Thanks for the reply from mark_endicott, please allow me to provide another insight.
Hi @y5famfnatudu ,

I'm not sure how your dataset is designed, here is my sample.

The data table is shown below.

Create a disconnected Date table.

Create a slicer using the 'Year' field in this Date table.
Using the 'Date' field in the Date table to calculate the 'Revenue CY' and 'Revenue PY' measures
This 'Date' field is filtered by the 'Year' field.

Revenue CY = CALCULATE(SUM('Table'[Amount]), 'Table'[Date] IN VALUES('Date'[Date]))

Revenue PY = 
VAR tb = PREVIOUSYEAR(VALUES('Date'[Date]))
RETURN
CALCULATE(SUM('Table'[Amount]),'Table'[Date] in tb)

In this way, these two measures get the correct result, and the year passed to the drillthrough page is the 'Year' field from the disconnected date table.

Use the following measure as a filter for the target visuals of the drillthrough page, set to is 1.

Measure = 
VAR selectedYear = SELECTEDVALUE('Date'[Year])
VAR selectedCompany = SELECTEDVALUE('Table'[Company])
RETURN
SWITCH(TRUE(),
selectedCompany = "C", IF(YEAR(MAX('Table'[Date])) = selectedYear-1, 1, 0),
selectedCompany <> "C", IF(YEAR(MAX('Table'[Date])) = selectedYear, 1, 0)
)

In the example, after clicking on row C and performing the drillthrough.
The drillthrougn page shows that the year 2025 is passed, but the visual shows the data for company C in 2024.

For other rows, the year of the data after the drill-though is the same as the passed year.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[y5famfnatudu]

Thank you @Anonymous and @mark_endicott , but I can't create any calculated tables, only measures are allowed

[mark_endicott]

@y5famfnatudu - Then you cannot solve this alone. You will need to speak with whoever has access to the semantic model file with the data table and ask them to add the additional table required.

For clarity, there is no other way to make this without the additional table.

[y5famfnatudu]

Thank you so much guys for your effort anyway, so much appreciated.
@mark_endicottand @Anonymous 

[mark_endicott]

@Anonymous - This is the solution I was alluding to with the video I have linked. But there is a problem with your solution I think, you have specified two conditions for Company C, I think the requirement was to always show the preivous year's invoices regardless of which company was selected for the drillthrough.

@y5famfnatudu  if this is the case the final filter measure just needs to be:

IF(YEAR(MAX('Table'[Date])) = SELECTEDVALUE('Date'[Year])-1, 1, 0)

I have re-attached the PBIX with this solution for you.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[mark_endicott]

You need to create a measure for amount specifically for use in the tables on the drill through. it will look something like this:

VAR _py = SELECTEDVALUE( YEAR( Table[Date] ) ) - 1

RETURN
CALCULATE( [Amount], ALL( Table[Date] ), YEAR(Table[Date]) = _py )

In order for this to work you need to make sure you users can select only a complete year in your slicer. If they can select a range of dates, you can implement variables to minus a year off the min and max dates of the range, then change the final filter in the CALCULATE().

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[y5famfnatudu]

Thank you @mark_endicott but that generates an error message:
This can happen when 2 measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

And even when I change 

YEAR ( Table[Date] )

to 

Table[Year]

which is a valid column, the measure at the end shows no values at all

[mark_endicott]

@y5famfnatudu - Sorry, I forgot YEAR( Table[Column] ) wouldnt work.

Have you ensured the column that is being filtered is inside the ALL(  ) ?? a.k.a if your slicer has Year in it then ALL( Table[Year] ).   

If this doesnt work, then it is because the other values from your table are being passed through as filters too. So you'll need to consider creating a duplicated table, like is shown in this video: https://www.youtube.com/watch?v=BG51W2lKYIA

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!