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
v-achippa's avatar
v-achippa
Icon for Community Support rankCommunity Support
1 year ago

Hi @richhthfc,

 

We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

 

Thanks and regards,

Anjan Kumar Chippa

v-achippa's avatar
v-achippa
Icon for Community Support rankCommunity Support
1 year ago

Hi @richhthfc,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

 

Thanks and regards,

Anjan Kumar Chippa