cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
almafater2
Helper I
Helper I

Average of 12 months plus last month of previous year

Hey guys,

 

I want to calculate an average of monthly values of GAV for a year. But instead of starting at January, start at December of the previous year and include all months of year (and for ongoing year all months to date) + december last year. It was so simple in excel, and I can't figure out a solution on PBI.
Here is a simple example of what I am trying to do:


https://www.dropbox.com/s/m8m312om0tfffxg/Example6.xlsx?dl=0


Any ideas how to set up the time inteligence for this?

 

2 ACCEPTED SOLUTIONS
Vera_33
Solution Sage
Solution Sage

Hi @almafater2 

 

Do you have Year column in your table, or you use a Date table? I am using the same to show you one way

 

Vera_33_0-1626253015714.png

Ave = 
VAR CurY=MAX(factTable[Year])
RETURN
AVERAGEX(FILTER(ALL('factTable'),factTable[Data]>=DATE(CurY-1,12,1)&&factTable[Data]<=DATE(CurY,12,31)),[GAV])

 

View solution in original post

Hi @almafater2 

 

Yes, your case is different from the simple Excel sample, so modified a little bit

Vera_33_0-1626311297835.png

Average GAV = 
VAR CurY=MAX(Dates[Year])
VAR T1 = CALCULATETABLE(ADDCOLUMNS(ALL(Dates[EndOfMonth]),"TEST",[GAV]),ALL(Dates[Year]))
RETURN
AVERAGEX(FILTER(T1,[EndOfMonth]>=DATE(CurY-1,12,1)&&[EndOfMonth]<=DATE(CurY,12,31)),[TEST])

 

View solution in original post

4 REPLIES 4
Vera_33
Solution Sage
Solution Sage

Hi @almafater2 

 

Do you have Year column in your table, or you use a Date table? I am using the same to show you one way

 

Vera_33_0-1626253015714.png

Ave = 
VAR CurY=MAX(factTable[Year])
RETURN
AVERAGEX(FILTER(ALL('factTable'),factTable[Data]>=DATE(CurY-1,12,1)&&factTable[Data]<=DATE(CurY,12,31)),[GAV])

 

View solution in original post

Hey, thank you a lot it does seem to work with the excel data, but my PBI model has a few extra details and for some reason it does not include the december of last year it seams. I am attaching an example file:
https://www.dropbox.com/s/a006u6lay2mnsvl/Example7.pbix?dl=0
In this case the expected value for 2021 would be 27176766,83.

Hi @almafater2 

 

Yes, your case is different from the simple Excel sample, so modified a little bit

Vera_33_0-1626311297835.png

Average GAV = 
VAR CurY=MAX(Dates[Year])
VAR T1 = CALCULATETABLE(ADDCOLUMNS(ALL(Dates[EndOfMonth]),"TEST",[GAV]),ALL(Dates[Year]))
RETURN
AVERAGEX(FILTER(T1,[EndOfMonth]>=DATE(CurY-1,12,1)&&[EndOfMonth]<=DATE(CurY,12,31)),[TEST])

 

View solution in original post

Worked like a charm, thank you 🙂 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors