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

Average Measure

Hello

 

I hope someone can help.

 

I want to create a measure to find the current average based on YTD data and that the average changes as the months go on. So as it stands I have three months of data Jan 2019 - March 2019 and a small amount of data from April. I only want to have the average of January - March and when April is finished, the numbers from April will be added to the calculation. Then May, June.....and so on. Is it a Rolling average?

 

Thanking you all in advance

Joe

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Average Measure

@PoweredOut 

See it all at work in the attached file (based on your pbix)

I think this will get you what you are looking for (YYTD average):

Measure3 =
VAR _LatestDataDate =
    MAX ( Actual[DATE] )
VAR _LatestMonthFinalDay =
    EOMONTH ( _LatestDataDate; 0 )
VAR _LatestDayForCalc =
    IF (
        _LatestDataDate = _LatestMonthFinalDay;
        _LatestMonthFinalDay;
        EOMONTH ( _LatestMonthFinalDay; -1 )
    )
RETURN
    IF (
        MAX ( 'DATE'[DATE] ) <= _LatestDayForCalc;
        AVERAGEX (
            ADDCOLUMNS (
                FILTER (
                    DISTINCT ( 'DATE'[Date.1].[Month] );
                    CALCULATE ( MAX ( 'DATE'[DATE] ) ) <= _LatestDayForCalc
                );
                "_Res"; [Percentage_Completed]
            );
            [_Res]
        )
    )

I also created another measure, similar to what you already have and to be used in the charts, that returns the measure value only for the months that are complete in the data:

Measure1 =
VAR _LatestDataDate =
    MAX ( Actual[DATE] )
VAR _LatestMonthFinalDay =
    EOMONTH ( _LatestDataDate; 0 )
VAR _LatestDayForCalc =
    IF (
        _LatestDataDate = _LatestMonthFinalDay;
        _LatestMonthFinalDay;
        EOMONTH ( _LatestMonthFinalDay; -1 )
    )
RETURN
    IF ( MAX ( 'DATE'[DATE] ) <= _LatestDayForCalc; [Percentage_Completed] )
4 REPLIES 4
Super User
Super User

Re: Average Measure

Hi @PoweredOut 

Please share sample data of the tables in your model if you want to get an accurate answer. With the little info you provide, I would venture the following measure in a card visual:

 

Measure =
VAR _LatestDate =
    MAX ( Table1[Date] )
VAR _LatestMonthFinalDay =
    EOMONTH ( _LatestDate, 0 )
VAR _LatestDateForCalc =
    IF (
        _LatestDate = _LatestMonthFinalDay,
        _LatestMonthFinalDay,
        EOMONTH ( _LatestMonthFinalDay, -1 )
    )
RETURN
    CALCULATE (
        AVERAGE ( Table1[Field_you_want_the_average_on] ),
        FILTER ( ALL ( Table1[Date] ), Table1[Date] <= _LatestDateForCalc )
    )

When you get the time, do read through these posting tips (particularly the bits in red). By following them, you will increase the probability of getting your questions answered quickly and you will make things much easier for people trying to help.

 

PoweredOut Regular Visitor
Regular Visitor

Re: Average Measure

Hello AIB

First thanks for the formula. I should have been clearer in my explaination.

 

I actually need to Average the result of a Measure that I have.

 

I have a two tables one with forecasted calls per month and another table with completed calls at various intervals.

 

I created two measures

 

My formula for completed calls is

     Calculate(
                  DISTINCTCOUNT(CALLS, [ID]),
                        FILTER(CALLS, [CALLSTATUSID] = "COMPLETED")

    Calculate(
                  SUMX(FORECASTEDCALLS, [CALLAMOUNT])
                        

I then created another Measure to work out the percentage of completed calls against the forecasted result. This works fine when in a line chart. So far so good. And from this final measure, I want to create an Average.

 

Looking at your I may need to create a table with Month and Year and then add the values from both tables?

 

Thanks

Joe

 

 

 

 

 

 

Highlighted
Super User
Super User

Re: Average Measure

@PoweredOut 

See it all at work in the attached file (based on your pbix)

I think this will get you what you are looking for (YYTD average):

Measure3 =
VAR _LatestDataDate =
    MAX ( Actual[DATE] )
VAR _LatestMonthFinalDay =
    EOMONTH ( _LatestDataDate; 0 )
VAR _LatestDayForCalc =
    IF (
        _LatestDataDate = _LatestMonthFinalDay;
        _LatestMonthFinalDay;
        EOMONTH ( _LatestMonthFinalDay; -1 )
    )
RETURN
    IF (
        MAX ( 'DATE'[DATE] ) <= _LatestDayForCalc;
        AVERAGEX (
            ADDCOLUMNS (
                FILTER (
                    DISTINCT ( 'DATE'[Date.1].[Month] );
                    CALCULATE ( MAX ( 'DATE'[DATE] ) ) <= _LatestDayForCalc
                );
                "_Res"; [Percentage_Completed]
            );
            [_Res]
        )
    )

I also created another measure, similar to what you already have and to be used in the charts, that returns the measure value only for the months that are complete in the data:

Measure1 =
VAR _LatestDataDate =
    MAX ( Actual[DATE] )
VAR _LatestMonthFinalDay =
    EOMONTH ( _LatestDataDate; 0 )
VAR _LatestDayForCalc =
    IF (
        _LatestDataDate = _LatestMonthFinalDay;
        _LatestMonthFinalDay;
        EOMONTH ( _LatestMonthFinalDay; -1 )
    )
RETURN
    IF ( MAX ( 'DATE'[DATE] ) <= _LatestDayForCalc; [Percentage_Completed] )
PoweredOut Regular Visitor
Regular Visitor

Re: Average Measure

Perfect!

 

Thank you so much. I really apprciate it.

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 394 members 4,426 guests
Please welcome our newest community members: