topic Re: Average Score over time with non-contiguous dates in DAX Commands and Tips

Average Score over time with non-contiguous dates

richhthfc — Thu, 06 Mar 2025 17:04:58 GMT

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.

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.

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.

Re: Average Score over time with non-contiguous dates

Greg_Deckler — Thu, 06 Mar 2025 18:11:31 GMT

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.

Re: Average Score over time with non-contiguous dates

Greg_Deckler — Thu, 06 Mar 2025 18:47:05 GMT

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

Re: Average Score over time with non-contiguous dates

v-achippa — Wed, 09 Apr 2025 14:22:37 GMT

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

Re: Average Score over time with non-contiguous dates

v-achippa — Tue, 15 Apr 2025 06:41:34 GMT

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

Re: Average Score over time with non-contiguous dates

v-achippa — Mon, 21 Apr 2025 13:31:29 GMT

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