3 Months Average of Sum

[Anonymous]

Hello all,

I want to create a simple report that has a line/column chart and a kpi on it. All I want to do is get the averag of the total of current month, next month, and the month after. I already have the sums for each month (you'll understand better when you see the pbix file), so I just gotta add those 3 numbers and divide them. Somehow, I managed that, but now I can't just get that average for the month that I received the file. Because I need that number, which is 2093, to divide the total quantity for the current month, which is 4285, so KPI should show 2.04 (See the the pbix file). 

Once you look at the pbix file you'll see what I'm trying to do. It should be easy but I'm not able come up with something. 

Thanks

[v-frfei-msft]

Hi @Anonymous ,

To create measures as below.

Measure = 
VAR D =
    FORMAT (
        MAXX ( ALL ( 'Inventory' ), 'Inventory'[File_Arrival_Date] ),
        "MM-YYYY"
    )
RETURN
    CALCULATE (
        SUM ( Inventory[Quantity_Available_Eaches] ),
        FILTER (
            'Inventory',
            FORMAT ( 'Inventory'[File_Arrival_Date], "MM-YYYY" ) = D
                && 'Inventory'[File_Arrival_Date] = MAX ( 'Inventory'[File_Arrival_Date] )
        )
    )

Measure 2 =
VAR D =
    FORMAT (
        MAXX ( ALL ( 'Inventory' ), 'Inventory'[File_Arrival_Date] ),
        "MM-YYYY"
    )
RETURN
    CALCULATE (
        [MSR_Next_3_Months_Forecast_AVG],
        FILTER ( 'Date', FORMAT ( 'Date'[CALENDAR_DT], "MM-YYYY" ) = D )
    )

Indicator = 
DIVIDE ( [Measure], [Measure 2] )

[Anonymous]

Thanks for the reply Frank, but when I highlight the columns from the visual, I get blank from the measures.