Is It Possible to Drill Through Last 12 Months

[russell80]

I have a matrix visual which shows products in rows and Year and Month in the columns, the values are the count of sales. e.g. it would show for example 10 bikes sold in Mar-2022.

What I want to do is be able to drill through and show on a table the 10 sales transactions from March plus all the transactions from the previous 11 months. It's easy to drill through and get the March sales, but I don't think it's possible to expand the date range on the drill through page unless someone knows a way?

[russell80]

Here's an example.

The matrix below shows the number of sales transactions by product and Year, Month. E.g. for Apr-22 there were 3 sales transaction of cars. The tooltip shows the qty of cars sold for the transactions for the last 12 months. So in April there were 11 cars sold in 3 transactions. What I want the tooltip to do is if I hover over another month, the date range changes to show a new 12 month period, with the new month selected as the last month in the range. e.g. if I hover over the cars row on Dec-21, I want the tool tip to show the qty fo car sales for Jan-21 to Dec-21, hope that makes sense.

[v-rzhou-msft · Accepted solution]

Hi @russell80 ,

I suggest you to create an unrelated date table and then create a measure to achieve your goal.

Calendar2:

Calendar2 = 
ADDCOLUMNS( CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMM"),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Measure:

Measure = 
VAR _SELECTMonth = MAX('Calendar'[Date])
VAR _12MONTHAGO = EOMONTH(_SELECTMonth,-12)
RETURN
IF(MAX(Calendar2[Date])>_12MONTHAGO&&MAX(Calendar2[Date])<=_SELECTMonth, CALCULATE(SUM('Table'[Qty]),FILTER(ALLEXCEPT('Table','Table'[Product]),YEAR('Table'[Date])*100+MONTH('Table'[Date]) = MAX(Calendar2[YearMonth]))))

Result:

We can see that report tooltip will show values in the last 12 months dynamiclly.

Best Regards,
Rico Zhou

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

[russell80]

Thanks Rico, that's worked really well!

[HenriqueReis]

Hi,

Can you give us a picture to better explanation?

Anyway, maybe you have a column with dates.

You can create a slicer and put your dates there and select the dates you want to show the results.

Like this: 

waiting for your return

Regards!