Last n months using drillthrough

[Anonymous]

Hi All,
I have a source page where i have a date slicer which shows the end of each month.
I have a matrix where i am having different categories and a measure which represents the value for the month that is selected in the slicer. I want to use the drillthrough function in the matrix table where i want to show the last n months value for the date which is selected in the source page.
Suppose i select march 2020 in the source page,i want to see the last 6 months in the target page when i use the drillthrough functionality.
Could anyone please suggest me any feasible solution

[amitchandak]

@Anonymous , if You are using a before slicer this one will work.

Measure =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = date(year(_max),month(_max)-6,1)
return
calculate([measure],filter(All(DATE), Date[Date] >=_min && Date[Date] <=_max))

or
Measure =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = date(year(_max),month(_max)-6,day(_max))
return
calculate([measure],filter(All(DATE), Date[Date] >=_min && Date[Date] <=_max))

Or use this solution: https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous]

Hi Amit,

Thanks for your quick response but unfortumately the measure which is supposed to be shown in the target page is a precomputed once where it has other if else statements grouped by other dimensions the same methodology is not working.

It would be great if you can suggest me any other alternative 

[v-lionel-msft]

Hi @Anonymous ,

This effect cannot be achieved using the ‘Drill through’ function, you may consider using bookmarks or sync slicer.

Create bookmarks in Power BI Desktop to share insights and build stories 

Sync slicers in Power BI visuals 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Could you provide some more insights on this as the sync slicer will only help to sync both the source and the target pages but as per the requirement the drillthrough action is only required to pass the specific date to the target page.

In the target page i want to show a line chart which will represent the last 6 months for the particular date which is selected in the source page for exmaple if march 2020 is selected as a slicer in the source page and when i use the drillthrough function in the matrix it will pass march 2020 as a reference date and show me a chart which will have values starting from oct 2020 till march 2020.

Hope that gives a clear picture what i want to achieve.
Please share your thiughts.

[Anonymous]

Hi @Anonymous ,
    I was also stucked in the same problem but this video helps me. 
Link: https://www.youtube.com/watch?v=duMSovyosXE
I hope this will help you too.

Thanks

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂