cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Rolling Average of Current & Prior Year that doesn't start until Year 2

I've read rolling average posts but can't find an answer to this one.

I want to calculate average common equity of a company as the current year equity plus prior year equity divided by 2.  I created a simple rolling average quick measure with 1 period prior and 0 periods forward.  Works great with ONE exception.  For the first year of observation, I want the rolling average to be null (blank) as it can't compute an average.  Instead, the quick measure just takes year 1 data.

Here is a sample of data for 1 company (the data set I have has thousands of companies and some have their first year in 2008, some have the first year in 2012, etc.)

 FiscalYear Common_Equity Current ROLLING QUICK MEASURE What I want 2008 20 20 [blank] 2009 40 30 30 2010 60 50 50 2011 80 70 70 2012 100 90 90 2013 120 110 110 2014 140 130 130 2015 160 150 150

This is my current rolling average Quick Measure code that was created when I did the quick measure:

``````Average of Common_Equity rolling average =
IF(
ISFILTERED('FS_Data'[FiscalYear]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFYEAR('FS_Data'[FiscalYear].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'FS_Data'[FiscalYear].[Date],
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(VALUES('FS_Data'), 'FS_Data'[FiscalYear].[Year]),
__DATE_PERIOD
),
CALCULATE(
AVERAGE('FS_Data'[Common_Equity]),
ALL(
'FS_Data'[FiscalYear].[QuarterNo],
'FS_Data'[FiscalYear].[Quarter],
'FS_Data'[FiscalYear].[MonthNo],
'FS_Data'[FiscalYear].[Month],
'FS_Data'[FiscalYear].[Day]
)
)
)
)``````

Is there a way to change this code so that I can say: Hey, if it is the first year of data, then the rolling average should be blank or null?

1 ACCEPTED SOLUTION
Community Support

Hi @janetcpa ,

``````Measure =
VAR x =
CALCULATE(
SUM('Table'[Common_Equity     ]),
FILTER(
ALL('Table'),
'Table'[FiscalYear       ] <= MAX('Table'[FiscalYear       ]) && 'Table'[FiscalYear       ] >= MAX('Table'[FiscalYear       ]) - 1
)
)
VAR __Min =
MINX(
ALL('Table'),
[FiscalYear       ]
)
RETURN
IF(
MAX('Table'[FiscalYear       ]) = __Min,
BLANK(),
DIVIDE( x, 2 )
)``````

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @janetcpa ,

``````Measure =
VAR x =
CALCULATE(
SUM('Table'[Common_Equity     ]),
FILTER(
ALL('Table'),
'Table'[FiscalYear       ] <= MAX('Table'[FiscalYear       ]) && 'Table'[FiscalYear       ] >= MAX('Table'[FiscalYear       ]) - 1
)
)
VAR __Min =
MINX(
ALL('Table'),
[FiscalYear       ]
)
RETURN
IF(
MAX('Table'[FiscalYear       ]) = __Min,
BLANK(),
DIVIDE( x, 2 )
)``````

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements