Create New Measure using Top 3 Highest Values of Another Measure Falling Within Specific Timeframe

[N823198]

Need to create Measure X that sums the top three values, occuring within the last 3 months, of Measure Y (that will filter down and or up timewise (wkly, mnthly, yrly). Simply, Measure Y is an average calc measure summing the 3 values found in Measure X to be divided by Related (count*hours)...[Measure X]/(Related(Table.Count)*Related(Table.Hours)...but not so simply as I am not figuring out how to create Measure X.

[v-kelly-msft]

Hi @N823198 ,

Is your issue solved now?If not,could you pls check my last reply?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[N823198]

Switching up original post a bit...MONTH column and COUNT column. I want to create a new measure for TREND column to calculate up to 3 month moving average of COUNT for each future month ignoring any values less than or more than 18% of the prior months' value.

Below is what I am trying to accomplish. 

[N823198]

Correction...

[v-kelly-msft]

Hi @N823198 ,

How to check whether the prior months' variance is over 18%+-?Can you show me the logic or calculation?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[N823198]

For May TREND...use March and April counts only as Feburary is outside variance

April - March 598/638 for a variance of 6%

April - February 24/638 for a variance of 96%

For June TREND...use May count only as April and March variance is greater than 18%

May - April = 638/793 for a variance of 20%

May - March = 598/793 for a variance of 25%

[v-kelly-msft]

Hi  @N823198 ,

Sorry for the late reply.

Do you wanna to calculate the data whose variance is below 18%?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[N823198]

Yes, but only up to the prior three months and if each prior month is over 18%, accept the last prior month's count.

[v-kelly-msft]

Hi @N823198 ,

Create 2 columns as below:

Trend =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[MonthNo]
                <= EARLIER ( 'Table'[MonthNo] ) - 1
                && 'Table'[MonthNo]
                    >= EARLIER ( 'Table'[MonthNo] ) - 3
        )
    )
VAR _previous =
    CALCULATE (
        MAX ( 'Table'[Count] ),
        FILTER ( 'Table', 'Table'[MonthNo] = EARLIER ( 'Table'[MonthNo] ) - 1 )
    )
VAR _previous_1 =
    CALCULATE (
        MAX ( 'Table'[Count] ),
        FILTER ( 'Table', 'Table'[MonthNo] = EARLIER ( 'Table'[MonthNo] ) - 2 )
    )
VAR _previous_2 =
    CALCULATE (
        MAX ( 'Table'[Count] ),
        FILTER ( 'Table', 'Table'[MonthNo] = EARLIER ( 'Table'[MonthNo] ) - 3 )
    )
RETURN
    IF (
        _previous = BLANK (),
        'Table'[Count],
        IF (
            _previous_1 = BLANK (),
            _previous,
            IF (
                _previous_2 = BLANK (),
                DIVIDE ( _previous + _previous_1, 2 ),
                IF (
                    ABS ( DIVIDE ( _previous_1, _previous ) - 1 ) < 0.18
                        && ABS ( DIVIDE ( _previous_2, _previous ) - 1 ) < 0.18,
                    DIVIDE ( _previous_1 + _previous_2, 2 ),
                    IF (
                        ABS ( DIVIDE ( _previous_1, _previous ) - 1 ) > 0.18
                            && ABS ( DIVIDE ( _previous_2, _previous ) - 1 ) > 0.18,
                        _previous,
                        IF (
                            ABS ( DIVIDE ( _previous_1, _previous ) - 1 ) < 0.18
                                && ABS ( DIVIDE ( _previous_2, _previous ) - 1 ) > 0.18,
                            DIVIDE ( _previous_1 + _previous, 2 ),
                            IF (
                                ABS ( DIVIDE ( _previous_1, _previous ) - 1 ) > 0.18
                                    && ABS ( DIVIDE ( _previous_2, _previous ) - 1 ) < 0.18,
                                DIVIDE ( _previous_2 + _previous, 2 )
                            )
                        )
                    )
                )
            )
        )
    )

% of trend to count = DIVIDE('Table'[Trend],'Table'[Count])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @N823198 ,

Could you pls provide some sample data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!