Rolling 12 month calculation of previous 12 month period

[Anonymous]

Hi,

I have a wish from a co-worker and I just don't seem to get his wish displayed correctly.

I need to display our notification totals (created and of which are closed) from past 12 months in a way that it shows the sum of the last 12 month period in a single visual. 

F.E. now I need to show a time period from February 2020 - January 2021 where the number shown in February 2020 is the amount of notifications created between March 2019 - February 2020. The number in March 2020 is from April 2019 - March 2020 etc. 

Currently I have managed to display the monthly data without the 12 month sums.

I have two tables, one which serves as datetable and one which have all the information regarding our notifications. Let's call datetable as Date and colum which has the dates [Dates]. Notification table has 100 000+ rows of data shown in below table (deleted about 20 columns from the table):

Notification ID	Created	Status
21509270	24.12.2019	Open
21477868	13.12.2018	Closed
21446008	28.11.2020	Open
21428464	20.1.2021	Closed
21387771	4.11.2017	Open
21336452	15.10.2018	Closed
21336453	15.10.2019	Open
21288502	28.1.2020	Closed
21237028	3.1.2021	Open
21237029	3.9.2020	Closed
21157503	30.1.2021	Open
21128599	14.12.2020	Closed

How can I make a visual I described above? With the above figures Jan2021 should show me total of 6, Dec 2020 4, Nov 2020 4, Oct 2020 3 etc (if I did my math ok 🙂 )

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Create a measure like below and add a relative date filter (in the last 12 calendar months) to the column chart to limit the axis. Here is the PBIX file for your reference.

Measure = CALCULATE(COUNT(Notification[Notification ID]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-1,YEAR))

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@Anonymous , the expected output is not clear, have you tried relative date slicer 

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

or this approach

https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous]

Expected output is a column chart where I'm showing the previous 12 calendar months. Each column should have the sum of created notifications from the past 12 month period.

Relative date filtering isn't acceptable as it cuts the data down to the last 12 months as I actually need the data from last 24 calendar months yet I only need to show in the chart 12 prior months. 

In the video, he doesn't show the sales as the previous 12 month sales. He only shows each months sales and just makes slicer which he can basically choose the 12 month period he likes. I don't need it like that.

I need to show the last 12 calendar months in a chart and in the chart it should have 12 columns with previous 12 months. At each month it shows the amount of notifications created in the last 12 month period of that month. For example today the chart would need to show last 12 calendar months February 2020 to January 2021. February 2020 would need to show the amount of notifications created between March 2019 thru February 2020 and January 2021 would need to show the amount of notifications created between February 2020 thru January 2021 etc.

Hopefully I did better job at explaining what I need to create.

[Anonymous]

Hi @Anonymous 

Still use the chart from @v-jingzhang with YYYYMM, how about changing the measure to:

Measure = 
VAR CurMonth = SELECTEDVALUE('Date'[Month])
VAR CurYear = SELECTEDVALUE('Date'[Year])
VAR MaxDate = EDATE(DATE(CurYear,CurMonth,1),1)
VAR MinDate = EDATE(DATE(CurYear,CurMonth,1),-11)
RETURN
CALCULATE(COUNTROWS('Table'),FILTER(ALL('Date'),'Date'[Date]>=MinDate&&'Date'[Date]<MaxDate))