cancel
Showing results for
Did you mean:
Highlighted
Post Patron

## Rolling average

Dear experts,

I am trying to calculate the rolling average of the last 12 months of the following measure:

Invoiced of CA = DIVIDE(  [Invoiced];[Contract Assets LM] ; 0)

This is the invoiced amount of the current month divided by the CA of the previous month.

I've tried the following:

Avrg Invoiced of CA 12M =
CALCULATE(
AVERAGEX(VALUES(Calendar[Date]);[Invoiced of CA]) ;
DATESINPERIOD(
Calendar[Date];
LASTDATE(Calendar[Date]);
-12;
MONTH
)
)

This is the result I'm getting:

I'm not sure if this is giving me the rolling average per day or something else. What I need is the rolling average per month of the last 12 months.

For reference, my calendar table looks like this:

Could somebody let me know what's wrong?

Thanks a lot!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I

## Re: Rolling average

Hi @setis

You're almost there but your AVERAGEX is iterating over Calendar[Date], I think this needs to be Calendar[Month].

Try this:

Avrg Invoiced of CA 12M =
CALCULATE (
AVERAGEX (
VALUES ( Calendar[Month] );
[Invoiced of CA]
);
DATESINPERIOD (
Calendar[Date];
LASTDATE ( Calendar[Date] );
-12;
MONTH
)
)

Best regards,

Martyn

Proud to be a Super User!

Highlighted
Super User IV

## Re: Rolling average

Values is used to use distinct values. Means if should group at day level

Avrg Invoiced of CA 12M =
CALCULATE(
AVERAGEX(VALUES(Calendar[Date]);[Invoiced of CA]) ;
DATESINPERIOD(
Calendar[Date];
LASTDATE(Calendar[Date]);
-12;
MONTH
)
)

Try

Avrg Invoiced of CA 12M =
CALCULATE(
AVERAGEX(VALUES(Calendar[Month-Year]);[Invoiced of CA]) ;
DATESINPERIOD(
Calendar[Date];
LASTDATE(Calendar[Date]);
-12;
MONTH
)
)

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

9 REPLIES 9
Highlighted
Super User I

## Re: Rolling average

Hi @setis

You're almost there but your AVERAGEX is iterating over Calendar[Date], I think this needs to be Calendar[Month].

Try this:

Avrg Invoiced of CA 12M =
CALCULATE (
AVERAGEX (
VALUES ( Calendar[Month] );
[Invoiced of CA]
);
DATESINPERIOD (
Calendar[Date];
LASTDATE ( Calendar[Date] );
-12;
MONTH
)
)

Best regards,

Martyn

Proud to be a Super User!

Highlighted
Super User IV

## Re: Rolling average

Values is used to use distinct values. Means if should group at day level

Avrg Invoiced of CA 12M =
CALCULATE(
AVERAGEX(VALUES(Calendar[Date]);[Invoiced of CA]) ;
DATESINPERIOD(
Calendar[Date];
LASTDATE(Calendar[Date]);
-12;
MONTH
)
)

Try

Avrg Invoiced of CA 12M =
CALCULATE(
AVERAGEX(VALUES(Calendar[Month-Year]);[Invoiced of CA]) ;
DATESINPERIOD(
Calendar[Date];
LASTDATE(Calendar[Date]);
-12;
MONTH
)
)

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Patron

## Re: Rolling average

Dear @MartynRamsden and @amitchandak

I'm getting the same result with both suggestions:

The total that I'm getting at the botom looks like the average of the first column. However the month values don't look right at all.

Highlighted
Super User I

## Re: Rolling average

Hi @setis

I'm pretty sure the problem here is the [Contract Assets LM] measure as it is being computed in the wrong filter context.

Please try the measure below. I haven't had chance to test it so can't be sure it will work:

Avrg Invoiced of CA 12M =
CALCULATE (
AVERAGEX (
VALUES ( Calendar[MonthYear] ),
"@Invoiced", [Invoiced],
"@ConAssLM", [Contract Assets LM]
),
"@InvoicedOfCA", DIVIDE ( [@Invoiced], [@ConAssLM] )
),
[@InvoicedofCA]
),
DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -12, MONTH )
)

If this doesn't work, please provide a copy of your pbix, excluding any sensitive data and I'll take another look.

Cheers.

Best regards,

Martyn

Proud to be a Super User!

Highlighted
Post Patron

## Re: Rolling average

@MartynRamsden , I get the exact same result as before.

Thanks a lot for trying. I'll try to prepare a similar scenario with fake numbers to reproduce the issue and I'll post it here.

Highlighted
Super User I

## Re: Rolling average

Hi @setis

I thought that might be the case but it was worth a shot!

I look forward to seeing your dummy data model.

In the meantime, are you able to share your DAX expression for the [Contract Asset LM] measure?

Best regards,

Martyn

Proud to be a Super User!

Post Patron

## Re: Rolling average

@MartynRamsden Sure!

As you mention the issue might be in the actual measure

The one I'm using is:

Contract Assets_LM = CALCULATE([Contract Assets_cum] ; PREVIOUSMONTH(Calendar_NAV[Date] ))

The Contract Assets cumulative is:

Contract Assets_cum =
VAR CumTilCM =
CALCULATE(
[Contract Assets_ALL];
FILTER(
ALL(Calendar_NAV[Date]);
Calendar_NAV[Date] <= MAX(Calendar_NAV[Date])))
RETURN
IF(
MAX(Calendar_NAV[IsCurrentMonth]) = 0 &&
MAX(Calendar_NAV[FutureDate]) = "Past"; CumTilCM;[CA_CurrentMonth])

Contract Assets_ALL is a measure with  no particular Calendar filter.

[CA_CurrentMonth] is a bit complicated, but I don't think that it is  affecting the results in this case.

Highlighted
Community Support

## Re: Rolling average

Hi setis,

If possible, could you please inform me more detailed information(such as your expected output and your sample data, you could use othe data instead of real data)? Then I will help you more correctly.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Highlighted
Post Patron

## Re: Rolling average

Dear @MartynRamsden , @amitchandak and @dax ,

I tried to build a dummy simplified data model and I could not reproduce the issue.

I just realized that the numbers that I was getting were correct and they looked so weird since they were affected by an internal adjustement on the previous year.

All in all, the measures and your suggestions were all correct. Thank you very much for your help!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### 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