Rolling average

[setis]

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!

[MartynRamsden · Accepted solution]

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.

[amitchandak · Accepted solution]

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

[setis]

Dear @MartynRamsden and @amitchandak 

Thanks a lot for your answers. 

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.

[MartynRamsden]

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.

[setis]

@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. 

[MartynRamsden]

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.

[setis]

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

[dax]

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.

[setis]

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!

[MartynRamsden · Accepted solution]

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.