Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
__Result
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
@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
@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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |