How to add a YTD drill-through to your Power BI P&L report

This is a question that came up from a client of mine. She had built out a P&L report and had added a drill-through page to show the underlying transactions. It worked fine for her monthly P&L, but not for her YTD P&L. The reason is this: the date selector for her YTD P&L was a month slicer that would then apply a filter to the drill-though page for the selected month (and not all of the months YTD).

Fortunately, you can solve this in much the same way that you would solve the problem of producing the initial YTD P&L report page; by creating a measure that uses CALCULATE to modify the filter context to YTD as opposed to a selected month. There also are a few additional things that you need to do, and I will explain them in this article.

Create a measure to calculate YTD profit & loss

You probably already have a YTD profit and loss measure (or equivalent) for your YTD P&L report page, and you will also need it for your YTD drill-through page. In this video, I have a generic P&L measure called [PLActual], that I then adapt to a YTD measure as follows:

YTD

This measure assumes that I have two slicers on the page; one month slicer that is giving me my MAX ( Dates[Date] ) value, and one financial year slicer that is giving me my MAX ( Dates[FY] ) value, where Dates[FY] refers to the financial year.

So, in the screenshot below, MAX ( Dates[Date] ) is 30th June 2019 and MAX ( Dates[FY] ) is 2020.

PL report

This [PLActualYTD] Measure is using the filter context given by the slicers (that on their own would just select June 2019) to determine the upper bound of the YTD range and the financial year. It is then using CALCULATE to create a new filter context for the measure that is “all dates in financial year 2020 that are less than or equal to 30th June 2019”.

The matrix and waterfall visualisations in the screenshot above are using [PLActualYTD] to calculate all of the YTD P&L values displayed.

Create your YTD drill-though page

Your YTD drill-through page is a separate report page that shows the low-level detail of your P&L transactions, most likely in a table. Here is the Microsoft documentation about how to create a drill-through page.

Follow these simple rules in setting up your drill-through table

To get your YTD drill-through page to work the way that you want, you will need to follow a few simple rules for your drill-through table as follows:

Use the date column from your Transactions table and not from your Dates table. This is because you want to filter this table, based on a modified filter context in [PLActualYTD] and not that provided by your Dates table (coming from the slicers on your main YTD P&L page).
Include [PLActualYTD] as a “column” in the drill-through transactions table. You may want to rename this something like “P&L Amount”. This is nice, as it gives a cross-reference amount for each line of the drill-through table, back to your YTD P&L report.
Add a visual-level filter to your drill-through table for the case [PLActualYTD] “is not blank”. In other words, the drill-through table will only show those transactions that contribute to the selected element of the YTD P&L report.
Remove all auto-summarizations on the fields in your drill-through table, except for the [PLActualYTD] column. This is for two reasons. Firstly, the purpose of this table is to deliver the underlying transactional detail and not summarisations. Secondly, these auto-summarizations will see the filter context given by the slicers from your YTD P&L page and (in the example above) only show transactions for June 2019.
Add [PLActualYTD] as the drillthrough field for the page.

drillthrough

Summary

This may all seem a little bit complicated, but actually, it is very simple. To help you, I have put together a short video that shows this step-by-step:

If you already have your YTD P&L report working, then you can quickly add a drill-through page like this one – showing the 34 transactions that make up the £8,668 for Nominal Code 4150, Department “Pharmacy” for April, May and June 2019.

YTD P&L page:

P&L page