{"id":3882,"date":"2020-05-26T16:35:14","date_gmt":"2020-05-26T16:35:14","guid":{"rendered":"https:\/\/sloane.co.uk\/finance\/blog-post\/financial-reporting-blog-post\/how-to-add-a-ytd-drill-through-to-your-power-bi-pl-report\/"},"modified":"2020-06-10T17:05:10","modified_gmt":"2020-06-10T17:05:10","slug":"how-to-add-a-ytd-drill-through-to-your-power-bi-pl-report","status":"publish","type":"post","link":"https:\/\/sloane.co.uk\/finance\/blog-post\/financial-reporting-blog-post\/financial-reporting-financial-reporting-blog-post\/how-to-add-a-ytd-drill-through-to-your-power-bi-pl-report\/","title":{"rendered":"How to add a YTD drill-through to your Power BI P&L report"},"content":{"rendered":"

How to add a YTD drill-through to your Power BI P&L report<\/p>\n<\/p>\n

\n

This is a question that came up from a client of mine. She had built out a P&L report and had added a\u00a0drill-through page<\/a>\u00a0to 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\u00a0for the selected month (and not\u00a0all of\u00a0the months YTD).<\/p>\n

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\u00a0also\u00a0are a\u00a0few\u00a0additional things that you need\u00a0to\u00a0do, and I will explain them\u00a0in this article.\u00a0<\/p>\n

Create a measure to calculate YTD profit & loss\u00a0<\/h3>\n

You probably already have\u00a0a 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\u00a0this video<\/a>, I have a generic P&L measure called [PLActual], that I then\u00a0adapt to a YTD measure as follows:\u00a0<\/p>\n

\"YTD\"<\/p>\n

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.\u00a0<\/p>\n

So, in the screenshot below, MAX\u00a0( Dates[Date] )\u00a0is 30th\u00a0June 2019 and MAX ( Dates[FY] ) is 2020.\u00a0<\/p>\n

\"PL<\/p>\n

This [PLActualYTD] Measure is using the\u00a0filter context<\/a>\u00a0given 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\u00a0using\u00a0CALCULATE<\/a>\u00a0to create\u00a0a new filter context for the\u00a0measure that is \u201call dates in financial year 2020 that are less than or equal to 30th\u00a0June 2019\u201d.\u00a0<\/p>\n

The matrix and waterfall visualisations in the screenshot above are using [PLActualYTD]\u00a0to calculate\u00a0all of\u00a0the YTD P&L values displayed.\u00a0<\/p>\n

Create your YTD drill-though page\u00a0<\/h3>\n

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\u00a0Microsoft documentation about how to create a drill-through page<\/a>.\u00a0<\/p>\n

Follow these simple rules in setting up your drill-through table\u00a0<\/h3>\n

To get your\u00a0YTD drill-through page\u00a0to work the way that you want, you will need to follow a few simple rules for your drill-through table as follows:\u00a0<\/p>\n

    \n
  1. 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\u00a0in\u00a0[PLActualYTD] and not that provided by your Dates table (coming from the slicers on your main YTD P&L page).\u00a0<\/li>\n
  2. Include [PLActualYTD] as a \u201ccolumn\u201d in the\u00a0drill-through transactions table. You may want to rename this something like \u201cP&L Amount\u201d. This is nice, as it gives a cross-reference\u00a0amount\u00a0for each line of the drill-through table, back to your YTD P&L report.\u00a0<\/li>\n
  3. Add a visual-level filter to your drill-through table for the case [PLActualYTD]\u00a0\u201cis not blank\u201d. In other words, the drill-through table will only show those transactions that contribute to\u00a0the selected element of the YTD P&L report.\u00a0<\/li>\n
  4. 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\u00a0of this table is\u00a0to deliver the underlying\u00a0transactional\u00a0detail\u00a0and not summarisations. Secondly, these auto-summarizations will see the filter context given by the slicers from\u00a0your YTD P&L page and (in the example above) only show transactions for June 2019.\u00a0<\/li>\n
  5. Add\u00a0[PLActualYTD] as the\u00a0drillthrough\u00a0field for the page.\u00a0<\/li>\n<\/ol>\n

    \"drillthrough\"<\/p>\n

    Summary\u00a0<\/h3>\n

    This may all seem a little bit complicated,\u00a0but actually, it\u00a0is very simple. To help you, I have put together\u00a0a short video that shows this step-by-step:<\/p>\n<\/p>\n

    If you already have your YTD P&L report working, then you can quickly add a drill-through page like this one \u2013 showing the 34 transactions that make up the \u00a38,668 for Nominal Code 4150, Department \u201cPharmacy\u201d for April, May and June 2019.\u00a0<\/p>\n

    YTD P&L page:<\/strong>\u00a0<\/p>\n

    \"P&L<\/p>\n

    Corresponding drill-through:<\/strong>\u00a0<\/p>\n

    \"drill-through\"<\/p>\n

    Useful links\u00a0<\/h3>\n

    DAX function reference<\/a>\u00a0<\/p>\n

    Power BI basics: an introduction to the Filter Context<\/a>\u00a0<\/p>\n<\/p><\/div>\n

    \u00a0<\/p>\n

    \u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

    Hugh Johnson explains how to show underlying transactions in a P&L report with a year-to-date\u00a0drill-through page.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[23],"tags":[],"_links":{"self":[{"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/posts\/3882"}],"collection":[{"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/comments?post=3882"}],"version-history":[{"count":0,"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/posts\/3882\/revisions"}],"wp:attachment":[{"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/media?parent=3882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/categories?post=3882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sloane.co.uk\/wp-json\/wp\/v2\/tags?post=3882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}