cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GGDAC Helper I
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    

 

 

 janfebmaraprmayjunjulaugsepoctnovdecresult
Total201015253060503595704030480
Average20101525306050359570403040
2 REPLIES 2
Super User IV
Super User IV

Re: Rolling 12 months total and Average

@GGDAC 

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.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


sanalytics Helper I
Helper I

Re: Rolling 12 months total and Average

@GGDAC ,
 Please follow the below steps,

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.

Hope it will help you..

Regards,
snandy
 
 
 
 
 



 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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