Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
richhthfc
New Member

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 of the date values are contiguous. So it may be that some ClientName have an entry every day, whilst others have an entry every other month.

I have simulated the data with this table.

 

ClientScoresOverTime = 
DATATABLE(
    "ClientName", STRING,
    "Date", DATETIME,
    "Score", INTEGER,
    {
        {"GoodClient","2025-01-12 00:00:00", 45},
        {"GoodClient","2025-02-24 00:00:00", 46},
        {"GoodClient","2025-03-02 00:00:00", 47},
        {"GoodClient","2025-04-14 00:00:00", 48},
        {"MediumClient","2025-01-12 00:00:00", 25},
        {"MediumClient","2025-02-12 00:00:00", 26},
        {"MediumClient","2025-03-06 00:00:00", 27},
        {"MediumClient","2025-04-17 00:00:00", 28},
        {"PoorClient","2025-01-12 00:00:00", 5},
        {"PoorClient","2025-02-05 00:00:00", 6},
        {"PoorClient","2025-03-02 00:00:00", 7},
        {"PoorClient","2025-04-24 00:00:00", 8}
    }
)

 

When I plot this in a over time, based on month. This works because there is at least one entry for each month and the report shows a gradual increase in the average score over time.
richhthfc_0-1741279694950.png

However the real data is more like this - some entries are missing. For example the GoodClient does not have an entry in Feb.

 

ClientScoresOverTime2 = 
DATATABLE(
    "ClientName", STRING,
    "Date", DATETIME,
    "Score", INTEGER,
    {
        {"GoodClient","2025-01-12 00:00:00", 45},
        {"GoodClient","2025-03-02 00:00:00", 47},
        {"GoodClient","2025-04-14 00:00:00", 48},
        {"MediumClient","2025-01-12 00:00:00", 25},
        {"MediumClient","2025-02-12 00:00:00", 26},
        {"MediumClient","2025-04-17 00:00:00", 28},
        {"PoorClient","2025-01-12 00:00:00", 5},
        {"PoorClient","2025-02-05 00:00:00", 6},
        {"PoorClient","2025-03-02 00:00:00", 7}
    }
)

 

This gives a distorted view of average as the rows are missing.

richhthfc_1-1741279859195.png

 

What I want is a Measure to calculate a score for a given date as the score carried forward from the last populated date.

I really want to be able to change the granularity of the report on the time axis - so by day, by week, by month.
I know that I could calculate and populate a table with every Client-Date combination at the minimum resolution required (for example every day), and use columns to calculate the effective score on every Date. But this will quickly become too big. If we have 2 years of data and 20,000 clients then this would be nearly 15million rows.

How can I do this with a Measure?

I'd like a CurrentScore measure that looks back in time for every single client, so that the average over time for whatever resolution the user chooses (Day, Week, Month, Year) is calculated according to all client scores?


I can't see how to do that. Any ideas?

Many thanks.
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
v-achippa
Community Support
Community Support

Hi @richhthfc,

 

Thank you for reaching out to Microsoft Fabric Community.

 

Thank you @Greg_Deckler for the prompt response.

 

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? or let us know if you need any further assistance.
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

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

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

Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

@richhthfc I did something similar to this using linear interpolation. https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Mind-the-Gap-Irregular-Time-Series/...

 

I'll see if I can take a closer look at your particular issue and find a more specific solution.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.