Rolling 12 months in table visualisation

[Anonymous]

Hi Team,

I have raw data like below table :- 

CaseId	Name	Staff	Line of Service	Violation History	Fine Amount	Date
11	Shubham	Partner	ABC	Matter Type A…	650	Jan
12	Shubham	Partner	ABC	Matter Type A…	650	Feb
13	Shubham	Partner	ABC	Matter Type A…	700	March
14	Shubham	Partner	ABC	Matter Type B…	0	Jan
15	Shubham	Partner	ABC	Matter Type B…	0	Feb
16	Shubham	Partner	ABC	Matter Type C…	500	Jan
17	Fayaz	Manager	PQR	Matter Type A…	650	Jan
18	Fayaz	Manager	PQR	Matter Type A…	650	Feb
19	Fayaz	Manager	PQR	Matter Type A…	700	March
20	Fayaz	Manager	PQR	Matter Type B…	0	Jan
21	Fayaz	Manager	PQR	Matter Type B…	0	Feb
22	Fayaz	Manager	PQR	Matter Type C…	500	Jan

and I wanted to create rolling 12 months output like below table :- 

Name	Staff	Line of Service	Violation History	Count of Cases	Total Amount
Shubham	Partner	ABC	Matter Type A…	3	3000
			Matter Type B…	2	0
			Matter Type C…	1	500
SubTotal					3500
Fayaz	Partner	PQR	Matter Type A…	3	3000
			Matter Type B…	20	0
			Matter Type C…	1	500
SubTotal					3500

Could you please help me on this ?

Thanks,

Shubham

[v-zhenbw-msft]

Hi @Anonymous ,

You need to have a date column to roll. We can use the following steps to meet your requirement.

1. Create a date table that doesn’t contain relationship.

date = 
ADDCOLUMNS(CALENDAR("2020/1/1","2020/12/31"),"month name",FORMAT([Date],"mmm"),"month number",MONTH([Date]))

2. Create two measures,

Count = 
var selected_ = MIN('date'[Date])
var selected_12 = DATE(YEAR(selected_)-1,MONTH(selected_),DAY(selected_))
return
CALCULATE(COUNT('Table'[Violation History]),FILTER('Table','Table'[Date]<=selected_ && 'Table'[Date]>=selected_12))

Total Amount = 
var selected_ = MIN('date'[Date])
var selected_12 = DATE(YEAR(selected_)-1,MONTH(selected_),DAY(selected_))
return
CALCULATE(SUM('Table'[Fine Amount]),FILTER('Table','Table'[Date]<=selected_ && 'Table'[Date]>=selected_12))

3. Then we can create a matrix table,

Turn off the stepped layout,

And in the subtotal, we can configure it as screenshot.

4. At last, we can add a slicer based on date table, the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

@v-zhenbw-msft Thank you so much for your solution. It was really helpful 🙂