Drill through - YTD details

Julia1234 — Fri, 22 Sep 2023 15:46:55 GMT

Good Day,

The report has "master" page with several slicers: Year, Month, Project, Category, Site;

This page also has matrix which uses drill thru.
Project | YTD Sales

Category |

Services

Installation | 1000

Config | 200

Asses | 100

The Details page (Drill Thru landing page) includes Project and Category drill thru fields and shows invoice details:

For example, with slicers Year=2023 Month=5 Site =NY, CA

2023 | 5 | NY |Services | Config | ABC | INV01 | $50

2023 | 5 | CA |Services | Config | XYZ | INV02 | $70

The ask is Details page to show YTD (or year to month) transactions only - so for the slicer listed above (Month =5) the details should include months:1,2,3,4,5.
How this could be achieved?
Thank you!

Re: Drill through - YTD details

Julia1234 — Fri, 22 Sep 2023 20:04:26 GMT

Found the solution https://www.accountingweb.co.uk/business/financial-reporting/how-to-add-a-ytd-drill-through-to-your-power-bi-pl-report

, posting it here, maybe this will be useful for anyone else.
in my case I created mesure to Sum invoice amt:
SumOfSales = SUM(Invoices[Invoice Total])

then created YTD measure and used it in Details page:
Sales YTD = CALCULATE ([SumOfSales],FILTER (ALL(Calendar), AND (Calendar[Date]<=MAX(Calendar[Date]), Calendar[Year]=Max(Calendar[Year])
)))

*Calendar table is used in Year, Month slicers. Calendar and Invoice tables connected be [Date] key field.

* all date fields in the Details page were from Invoices table and not Calendar.

topic Drill through - YTD details in DAX Commands and Tips

Drill through - YTD details

Re: Drill through - YTD details