Forum Discussion

richhthfc's avatar
richhthfc
New Member
1 year ago
Solved

Average Score over time with non-contiguous dates

Hi, I'm struggling to work out how to do this and would appreciate any advice. I have a table that has ClientName, Date and Score. I want to plot the average score over time. However, not all...
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    richhthfc Came up with this:

    Measure = 
    VAR __MonthNo = MAX( 'ClientScoresOverTime2'[MonthNo] )
    VAR __Clients = DISTINCT( ALL('ClientScoresOverTime2'[ClientName]) )
    VAR __Months = DISTINCT( ALL( 'ClientScoresOverTime2'[MonthNo]) )
    VAR __Table = SELECTCOLUMNS( CROSSJOIN( __Clients, __Months ), "__ClientName", [ClientName], "__MonthNo", [MonthNo] )
    VAR __Table1 = ADDCOLUMNS( __Table, "__Value", MAXX( FILTER( ClientScoresOverTime2, [ClientName] = [__ClientName] && [MonthNo] = [__MonthNo] ), [Score] ) )
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table1,
            "__Value2",
                IF( 
                    [__Value] <> BLANK(), 
                    [__Value], 
                    SUMX( FILTER( ALL('ClientScoresOverTime2'), [ClientName] = [__ClientName] && [MonthNo] = [__MonthNo] - 1 ), [Score])
                )
        )
    VAR __Table3 = FILTER( __Table2, [__MonthNo] = __MonthNo )
    VAR __Result = AVERAGEX( __Table3, [__Value2] )
RETURN
    __Result
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
1 year ago

richhthfc Came up with this:

Measure = 
    VAR __MonthNo = MAX( 'ClientScoresOverTime2'[MonthNo] )
    VAR __Clients = DISTINCT( ALL('ClientScoresOverTime2'[ClientName]) )
    VAR __Months = DISTINCT( ALL( 'ClientScoresOverTime2'[MonthNo]) )
    VAR __Table = SELECTCOLUMNS( CROSSJOIN( __Clients, __Months ), "__ClientName", [ClientName], "__MonthNo", [MonthNo] )
    VAR __Table1 = ADDCOLUMNS( __Table, "__Value", MAXX( FILTER( ClientScoresOverTime2, [ClientName] = [__ClientName] && [MonthNo] = [__MonthNo] ), [Score] ) )
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table1,
            "__Value2",
                IF( 
                    [__Value] <> BLANK(), 
                    [__Value], 
                    SUMX( FILTER( ALL('ClientScoresOverTime2'), [ClientName] = [__ClientName] && [MonthNo] = [__MonthNo] - 1 ), [Score])
                )
        )
    VAR __Table3 = FILTER( __Table2, [__MonthNo] = __MonthNo )
    VAR __Result = AVERAGEX( __Table3, [__Value2] )
RETURN
    __Result