Average of cumulative values

[TessBroersen]

Hi there! 🙂

I would like to calculate the average of the results of a cumulative measure that is called Quantity Cumulative. The idea is to retrieve the results of that measure from the last 90 days. So for each of those 90 days I need to retrieve the result of that Quantity Cumulative measure. The next step that needs to be taken, is to take the average of those 90 values. 

This is what the Quantity Cumulative measure looks like: 

Quantity Cumulative =

VAR DateSelectedByEndUser =

    MAX ( Dim_Calendar[DateValue_Date] )

VAR LastPhysicalDate =

    CALCULATE (

        MAX ( FACT_Vw_Inventory[Physical Date] ),

        REMOVEFILTERS ()

    )

VAR Result =

    IF (

        [Start Date] <= LastPhysicalDate,

        CALCULATE (

            [Quantity],

            Dim_Calendar[DateValue_Date] <= DateSelectedByEndUser

        )

    )

RETURN

    Result

I've been racking my brain for hours, but I can't seem to figure it out. I've tried multiple measures that include CALCULATE, DATESINPERIOD, AVERAGEX, etc., but nothing seems to work. A couple of examples of the measures I tried:

TEST 1 AVG Cumulative Quantity 3M =

DIVIDE(CALCULATE(

    [Quantity Cumulative],

    DATESINPERIOD (

            Dim_Calendar[DateValue_Date],

            MAX ( Dim_Calendar[DateValue_Date] ),

            -90,

            DAY

        )

    ),

    - 90)

TEST 2 AVG Cumulative Quantity 3M =

VAR LastSelectedDate =

    MAX ( Dim_Calendar[DateValue_Date] )

VAR Period =

    DATESINPERIOD ( Dim_Calendar[DateValue_Date], LastSelectedDate, -90, DAY )

VAR Result =

    CALCULATE (

        AVERAGEX ( VALUES ( Dim_Calendar[DateValue_Date] ), [Quantity Cumulative] ),

        Period

    )

RETURN

    Result

Any help is highly appreciated! 

[TessBroersen]

@amitchandak 

Thanks for your help, it worked 🙂

The only thing that keeps surprising is me is that the two options you showed me, both have very different outcomes... 

The second option was the one that worked for me, so thanks a lot! 

[TessBroersen]

Hi Amitchandak,

Thanks for your reply!

You've sent me three DAX-measures. The first one results in different numbers than the second one combined with the third. 

The measures I've tried before resulted in the same numbers however. 

I still have no idea what numbers are the correct ones to be honest. Your first option results in higher numbers for some Article Numbers, but for other Article Numbers it results in the exact same numbers as your third option does. 

[amitchandak · Accepted solution]

@TessBroersen , Make sure Dim_Calendar is marked as date table and in visual period from Dim_Calendar is used

Try

CALCULATE (
AVERAGEX ( VALUES ( Dim_Calendar[DateValue_Date] ), CALCULATE ([Quantity], filter(all(Dim_Calendar), Dim_Calendar[DateValue_Date] <= max(Dim_Calendar[DateValue_Date]) ))),
DATESINPERIOD ( Dim_Calendar[DateValue_Date], MAX ( Dim_Calendar[DateValue_Date] ), -90, DAY )
)

cumm = CALCULATE ([Quantity], filter(all(Dim_Calendar), Dim_Calendar[DateValue_Date] <= max(Dim_Calendar[DateValue_Date]) ) )

agv=

CALCULATE (
AVERAGEX ( VALUES ( Dim_Calendar[DateValue_Date] ), [Cumm]),
DATESINPERIOD ( Dim_Calendar[DateValue_Date], MAX ( Dim_Calendar[DateValue_Date] ), -90, DAY )
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.