Forum Discussion
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}
}
)
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.
What I want is a Measure to calculate a score for a given date as the score carried forward from the last populated date.
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.
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
5 Replies
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/m-p/991790#M436
I'll see if I can take a closer look at your particular issue and find a more specific solution.
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 __ResultHi 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
