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

RA.PNG

 

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:

calendar.PNG

 

Could somebody let me know what's wrong?

 

Thanks a lot!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
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

 

If I answered your question, please help others by accepting it as a solution.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
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
    )
)


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!

View solution in original post

9 REPLIES 9
Highlighted
Super User I
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

 

If I answered your question, please help others by accepting it as a solution.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
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
    )
)


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!

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Rolling average

Dear @MartynRamsden and @amitchandak 

Thanks a lot for your answers. 

I'm getting the same result with both suggestions:

RA3.PNG

 

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
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 (
        ADDCOLUMNS (
            ADDCOLUMNS (
                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

 

If I answered your question, please help others by accepting it as a solution.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
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
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

 

If I answered your question, please help others by accepting it as a solution.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
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. 

 

Thanks in advance!

Highlighted
Community Support
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.

Please do mask sensitive data before uploading.

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

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!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors