Build Running Total Chart that can Filter Across the Page

[superhayan]

Hello, I have a list of deal records as raw data and I have built a chart of 12 month running total deal count against close date like below. The 07 May 24 bar shows the deal count closed during 8 May 23 to 7 May 24; and the 06 May 24 bar shows the deal count closed during 7 May 23 to 6 May 24... etc.

I have also built a table in the same page that hold all the deal records such as deal name, deal region, deal size etc. Noramlly when I click on a visual, it will filter across all visuals on the same page if they share the same underlying data. Now when I click on the 07 May 24 bar, the table will only show the deals that have close date as 7 May 24. However, as the bar represent the 100 deals that are closed in the past 12 months from 7 May 24, I want the table to show the corresponding 100 deals instead. Is there any way to achieve this?

Thank you!

[Ashish_Mathur]

Hi,

Ensure that you have a Calendar Table with a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag the Date column from the Calendar Table.  Write this measure and drag it to your Table visual

Measure = calculate(sum(Data[Amount]),datesbetween(calendar[date],edate(min(calendar[date]),-12)+1,max(calendar[date])))

[superhayan]

Hi Ashish,

Thanks for your reply. Sorry that I think you might have misunderstood my question.

I know how to get the 12 month rolling sum. My issue is that my page will have 2 visuals: (1) bar chart of 12m rolling total deal count against close date; (2) A table of the deal data (deal name, deal size, close date.... etc) without any calculations. My ultimate goal is that when I click on any bar in the bar chart, say the bar of date 7 May 2024 (which represent the total count of deals closed during 8 May 23 - 7 May 24), the table will be filtered to get the deal records that make up that bar. I cannot achieve this result - now when I click on the 7 May 2024 bar, the table will only show all the deals with close date on 7 May 2024. 

Sorry if I did not make my question easy to understand. Do you know how can I achieve the result I want?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.