cancel
Showing results for
Did you mean:
Highlighted
Helper I

Rolling 12 months total and Average

Dear Team I have below table with answer which I want to derive in my meansure. I want last 12 months sales total and monthly average based on my date table which is displayed in my slicer

Month - Dec

Year - 2019

 jan feb mar apr may jun jul aug sep oct nov dec result Total 20 10 15 25 30 60 50 35 95 70 40 30 480 Average 20 10 15 25 30 60 50 35 95 70 40 30 40
2 REPLIES 2
Super User IV

Re: Rolling 12 months total and Average

Try like

``````Sales Value Last 12M =
VAR  _End = EOMONTH(Sales[Date],0)
VAR  _Star = DATE(YEAR(_EndDate),MONTH(_EndDate) - 12,1)
RETURN
CALCULATE(SUM(Sales[Value]), DATESBETWEEN(Sales [Date], _Start, __End))

Average Value Last 12M =
VAR  _End = EOMONTH(Sales[Date],0)
VAR  _Star = DATE(YEAR(_EndDate),MONTH(_EndDate) - 12,1)
RETURN
CALCULATE(Average(Sales[Value]), DATESBETWEEN(Sales [Date], _Start, __End))``````

in matrix you can use show on row to show you measure on the row. And put month on column.

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper I

Re: Rolling 12 months total and Average

@GGDAC ,

first, create a calculated table with your Month Name (like, Jan, Feb etc) and month Number. (Make sure, this calculated table should not have any relationship with your date table)
Put the calculated table's month name into a matrix visual.
I think you have already put Year and Monthname from your date table into slicer and selected your desire month and year.
Now, create below measures,

Last 12 Months Sale = CALCULATE([Total Sales],DATEADD('Calendar'[Date],-12,MONTH) )

Total Sales  =
CALCULATE([Last 12 Months Sale],FILTER(ALL('Calendar'),CONTAINS(VALUES('Table'[Month]),'Table'[Month],'Calendar'[Month]) && MAX('Table'[Index])<=MAX('Calendar'[MonthNum]) && 'Calendar'[Year]=MAX('Calendar'[Year]) ) )

Now, Drag the total sales into matrix visual which you have created by your calculated table month name. It will show your desired result.
I think, you can achieve the average part same way.

Regards,
snandy

Announcements

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors