cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Matrix with dates and different average columns

Hello everyone,

I need your help to build a somewhat special matrix, like the one shown in the following image.  This matrix shows the last 15 months of history, the average of the last 3 months, average of the last 12 months and the average of the entire history of the data.

 

1.PNG

 

I think the only way to build it is with a support table to generate the desired columns, but I need the periods to be mobile.

 

In Table 2 of the pbix file I have solved the moving periods, but I need to add the averages columns!!!

 

Any help will be highly appreciated. Thanks a lot! 

 

PBIX File 

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Matrix with dates and different average columns

@cheyzaguirre , If you are looking for the format, I doubt that. I created an idea for that

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39773011-hybrid-table

You can get month like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
3rd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))

4th last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-4,MONTH)))

and so on, if you need simple avg  -- Over all

Cumm Sales = CALCULATE(Average(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(Average(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

 

Rolling

 

 

Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))

Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Matrix with dates and different average columns

Thank you for your answer, but it does not completely solve my problem since selecting a month filters the matrix and only shows the selected period. I need to select a month to show from the selected month back all the measurements

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors