question about drillthrough for specific measure

[AndriyK]

Hello,

i would like to create report, in which users can select Year and Month and see the values for last full 6 months up to and including the selected year and month. For example if the user selects Dec 2008, the report should to show full months from July to Dec 2008.  if the user selects June 2008, the the report would show data from January to June 2008.

from this report i would like to be able to drillthrough to another page to see the details for particular month.

i was able to get the first page working, but having issues with drillthrough page.

i believe i know what is the issue: it is a granularity between my tables, but can't wrap my head on how to fix it.

hopefully somebody can direct me in a right direction or tell me that it is not possible...

for testing I'm using the simplified sample database that i've downloaded from DAX paterns website created by SQLBI.
i've created a calculated table Year Month and inactive relationship 'Year Month'[Year Month Number] -> 'Date'[Year Month Number]. the 'Year Month' table is

SUMMARIZE ( 'Date', 'Date'[Year Month Number], 'Date'[Year Month Key] )

on the main page i placed 2 single select slicers for Year and Month and table with customer[CountryRegion] on rows and Year Month[Year Month Key] on columns and the measure Sales 2

Sales 2 := VAR MaxYearMonth = MAX('Date'[Year Month Number] ) VAR MinYearMonth = ( MaxYearMonth - 5 ) VAR SelectedYearMonths = FILTER ( 'Year Month', 'Year Month'[Year Month Number] <= MaxYearMonth && 'Year Month'[Year Month Number] >= MinYearMonth ) VAR Result = CALCULATE ( [Sales Amount], ALL ( 'Date' ), SelectedYearMonths, USERELATIONSHIP ( 'Date'[Year Month Number], 'Year Month'[Year Month Number] ) ) RETURN Result

looks like the main page works as i want.

the drillthrough page works fine as far as i don't bring the date[date] column to the table on drillthrough page. 

when i bring the date to the table it shows the correct totals, but the rows are not correct.

i believe the issue is the relationship granularity between 'Year Month' table and Date tables.

i would like to be able to show date[date] on the drillthough page/table.
i have tried to create different measures to pass the context to filter the dates correctly, but no luck.

i can't figure it out.

could you please let me know if it is possible to do that.

the link to the test file https://www.dropbox.com/s/kbj7r4tus6rg6jm/Test%2020230215.pbix?dl=0 

thank you,

Andriy 

[AndriyK]

looks like i figured it out:

i had to use the date column from Sales table and not Date[date] column as i was originally using. looks like now it is working as expected.

[amitchandak]

@AndriyK , refer this, the measure has to be part of visual in drill through

Power BI Drill Through when Userelationship is used: https://youtu.be/1BM_WGATrQA

[AndriyK]

hi @amitchandak ,
thanks for replying!

i've taken look at the video, but I don't think it has an answer/hint on how to resolve the problem i'm working on.