Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 o...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Anonymous 

    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] )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

 

 

 

 

 

 

AlB's avatar
AlB
Community Champion
7 years ago

Anonymous 

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] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Perfect!

     

    Thank you so much. I really apprciate it.