cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Agenor Regular Visitor
Regular Visitor

3 Months Average of Sum

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

2 REPLIES 2
Community Support Team
Community Support Team

Re: 3 Months Average of Sum

Hi @Agenor ,

 

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

Capture.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Agenor Regular Visitor
Regular Visitor

Re: 3 Months Average of Sum

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 215 members 2,161 guests
Please welcome our newest community members: