Drill-through date range (list of items after a given date)

Power BI
1

Hi, I hope you can help me with this problem. I have a report where I’m manually defining release dates of SW products. We are interested in the number of new work items opened in 30 and 90 days after each release.

image
image2815×648 45.2 KB

This part I managed, however now I need to be able to click on the 30-90 columns and drill through to work item details, to a list of work items from the selected date +30 or +90 days period. Right now it’s showing only one day, I need the whole period. Any help would be highly appreciated!
Thanks and let me wish to this wonerful community all the best into 2022! :slight_smile:
Nik

Link to the pbix file

2

Hello @nemethn,

Thank you, I wish you all the best for 2022 too :slight_smile:

I managed to solve your problem by using the technique of Dynamic Grouping.
I was searching for a way to filter your drill through page in order to have only the items that answer to your criteria.

The second problem was that you have three different date release that needs to work separately. That is why I’ve created three different grouping tables and three different measures.

The grouping tables are like that

image
image821×460 13.2 KB

Then I’ve created the measure that calculates the number of items that answer your criteria in a day range of 30 or 60 days.

image
image931×334 11.8 KB

With the Groups column of Dynamic Grouping table, I can now show the number of items in a bar chart.

image
image1796×513 66.2 KB

The final step is the drill trough page. I also needed to create three different drill trough pages and apply for each the right filter.
Each time I allow drill through filter for the right “Groups” column of the right Dynamic Grouping table.

For example for the date release 1, I use “Groups” of Dynamic Grouping 1 and filter the measure “Nb Dynamic 1” to only show the items in the criteria

image
image1821×824 201 KB

I hope this will help you and don’t hesitate to ask me questions if needed.

Here is the Pbix file :wink:
tmp.pbix (512.6 KB)

All the captures are about 60 days because at the beginning I’ve worked with 60 days, but in the file is 90 days :wink:

Best regards,
JBocher

3

Thanks a lot @JBocher !
Your grouping approach is very interesting, and I will definitely spend time tonight to fully understand it. For now, as I have to present a working solution in the next 20 minutes, I came up with a similar approach. I have created 3 new drill through pages, but I use a less sophisticated solution with synced slicers and bookmarks and I’m filtering the details table with the following slicer (different measure for each table):
WorkItems R1 30d =
VAR ReferenceDate = SELECTEDVALUE(ReleaseDate1[Date])
VAR NextPeriod =
DATE ( YEAR ( ReferenceDate ), MONTH ( ReferenceDate ), DAY ( ReferenceDate ) + 30 )

RETURN
IF(MIN(WorkItems[CreatedDate])>= ReferenceDate && min(WorkItems[CreatedDate])<= NextPeriod,1,0)

The end result:

image
image1996×576 66.7 KB

Thank you for your help! :slight_smile:

4

It’s a good solution too :slight_smile:
It depends if you want to use the drill through functionality offers by Power BI

Bookmarks and buttons are often use to do things that are not possible or difficult to do with Drill through :wink:

Best regards,
JBocher

5

It’s great to know that you are making progress with your query @nemethn. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!