DAVEBOTs ELO Database

With the new year we’re moving away from the Danish ELO database to one that’s directly under our control. In theory it should do the same thing, provide a unified ELO score by player. The database should update daily, provide scores daily and allow us to see the ELO on the key dates listed in the charter as well as provide the average to allow everyone to see where we’re all at. This is no slight on the Danish system which is fantastic but we wanted something that we could control in case they don’t update theirs for whatever reason.

This system was put in place last year and ran concurrently with the Danish system so we could track and monitor it. Wisely, the outgoing Captain Gav suggested that rather than have this new system exist solely in my head we detail how it works to allow us to have redundancy in the system and a way of replicating it when he murders me (or something).

My way of doing things is probably very far from perfect and by putting it here there may well be errors or inconsistencies that people may spot or even much better ways of doing things. I am a nerdy amatuer at best so please do feel free to reach out to tell me if that is the case.

In the meantime, this is what I’ve got going on.

First I created a database in SQL on my windows machine. It’s rather imaginatively called BLOODBOWL. I then created the following folders in my OneDrive in a folder called ELO:
CleanData, Download, Errors, ExtractedData, Formats & SQLBackup

I have created a scheduled task to run the RunEverything.bat file which contains this script at 9:30 every day:

@echo off

REM Step 1: Run PowerShell script to Download, Extract, and Clean
powershell.exe -ExecutionPolicy Bypass -File "C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanAndLoadData.ps1"

REM Step 2: Wait a bit to ensure PowerShell script completes
timeout /t 5 /nobreak

REM Step 3: Execute first stored procedure to reload base tables
sqlcmd -S C0ZZ4F3\BLOODBOWL -d BloodBowl -E -Q "EXEC dbo.ReloadNAFDataAndRebuildELO"

REM Step 4: Wait again before next operation
timeout /t 5 /nobreak

REM Step 5: Execute second stored procedure to rebuild Elo logic
sqlcmd -S C0ZZ4F3\BLOODBOWL -d BloodBowl -E -Q "EXEC dbo.RebuildELO"

REM Done
exit

Step 1 of that is to run the CleanAndLoadData Powershell script. This downloads the daily file from the NAF daily data dump, extracts the zip file, flattens the files and cleans the data in them

# ========================
# CleanAndLoadData.ps1
# ========================

# Define paths
$BaseFolder = "C:\Users\david.ridley\OneDrive\BB\Reports\ELO"
$DownloadUrl = "https://member.thenaf.net/glicko/nafstat-tmp-name.zip"
$DownloadPath = Join-Path $BaseFolder "Download\NAFData.zip"
$ExtractPath = Join-Path $BaseFolder "ExtractedData"
$CleanPath = Join-Path $BaseFolder "CleanData"
$LogPath = Join-Path $BaseFolder "Log.txt"

# Start logging
"===== CleanAndLoadData.ps1 started at $(Get-Date) =====" | Out-File -FilePath $LogPath -Encoding utf8 -Append

try {
    # Step 1: Download
    Write-Output "Downloading ZIP file..."
    "[$(Get-Date)] Downloading ZIP..." | Out-File -FilePath $LogPath -Encoding utf8 -Append
    Invoke-WebRequest -Uri $DownloadUrl -OutFile $DownloadPath -UseBasicParsing
    "[$(Get-Date)] Download completed: $DownloadPath" | Out-File -FilePath $LogPath -Encoding utf8 -Append

    # Step 2: Extract
    Write-Output "Extracting ZIP file..."
    "[$(Get-Date)] Extracting ZIP to: $ExtractPath" | Out-File -FilePath $LogPath -Encoding utf8 -Append
    if (Test-Path $ExtractPath) {
        Remove-Item "$ExtractPath\*" -Recurse -Force -ErrorAction SilentlyContinue
    } else {
        New-Item -Path $ExtractPath -ItemType Directory | Out-Null
    }
    Expand-Archive -Path $DownloadPath -DestinationPath $ExtractPath -Force
    "[$(Get-Date)] Extraction completed." | Out-File -FilePath $LogPath -Encoding utf8 -Append

    # Step 3: Flatten extracted structure
    Write-Output "Flattening extracted files..."
    $SubFolder = Get-ChildItem -Path $ExtractPath -Directory | Select-Object -First 1
    if ($SubFolder) {
        Get-ChildItem -Path $SubFolder.FullName -Filter "*.csv" | ForEach-Object {
            Move-Item $_.FullName -Destination $ExtractPath -Force
        }
        Remove-Item -Path $SubFolder.FullName -Recurse -Force
    }
    "[$(Get-Date)] Flattened extracted files." | Out-File -FilePath $LogPath -Encoding utf8 -Append

    # Step 4: Clean
    Write-Output "Cleaning extracted CSV files..."
    "[$(Get-Date)] Cleaning CSV files..." | Out-File -FilePath $LogPath -Encoding utf8 -Append
    if (!(Test-Path $CleanPath)) {
        New-Item -Path $CleanPath -ItemType Directory | Out-Null
    }

    $ColumnsToRemove = @(
        'tournamentaddress1','tournamentaddress2','tournamentcity','tournamentstate','tournamentzip',
        'tournamenturl','tournamentnotesurl','tournamentstartdate','tournamentenddate','tournamenttype',
        'tournamentstyle','tournamentscoring','tournamentcost','tournamentnaffee','tournamentnafdiscount',
        'tournamentinformation','tournamentcontact','tournamentemail','tournamentorg','geolongitude',
        'geolattitude','tournamentreport','subscription_closed','variant_notes','tournament_ruleset_file'
    )

    $Files = Get-ChildItem -Path $ExtractPath -Filter "*.csv"
    foreach ($File in $Files) {
        try {
            $OutputPath = Join-Path $CleanPath $File.Name
            $Lines = Get-Content $File.FullName
            if ($Lines.Count -eq 0) { continue }

            $Header = $Lines[0] -replace '"', ''
            $AllColumns = $Header -split ';'
            if ($File.Name -ieq "naf_tournament.csv") {
                $KeepIndexes = @()
                for ($i = 0; $i -lt $AllColumns.Length; $i++) {
                    if ($ColumnsToRemove -notcontains $AllColumns[$i]) {
                        $KeepIndexes += $i
                    }
                }

                $NewHeader = ($KeepIndexes | ForEach-Object { $AllColumns[$_] }) -join ';'
                $CleanedLines = @($NewHeader)

                for ($j = 1; $j -lt $Lines.Count; $j++) {
                    $Cols = ($Lines[$j] -replace '"', '') -split ';'
                    if ($Cols.Count -ne $AllColumns.Count) { continue } # skip malformed
                    $Cleaned = ($KeepIndexes | ForEach-Object { $Cols[$_] }) -join ';'
                    $CleanedLines += $Cleaned
                }

                $CleanedLines | Set-Content -Path $OutputPath -Encoding utf8
            } else {
                $Header = $Lines[0] -replace '"', ''
                $DataLines = $Lines[1..($Lines.Count - 1)] | ForEach-Object { ($_ -replace '"', '') }
                $Header | Set-Content -Path $OutputPath -Encoding utf8
                $DataLines | Add-Content -Path $OutputPath -Encoding utf8
            }

            "[$(Get-Date)] Cleaned: $($File.Name)" | Out-File -FilePath $LogPath -Encoding utf8 -Append
        }
        catch {
            "[$(Get-Date)] ERROR cleaning file $($File.Name): $_" | Out-File -FilePath $LogPath -Encoding utf8 -Append
        }
    }

    Write-Output "CSV cleaning completed."
    "[$(Get-Date)] Cleaning completed successfully." | Out-File -FilePath $LogPath -Encoding utf8 -Append

    # Step 5: Done
    Write-Output "Script completed successfully."
    "[$(Get-Date)] Script completed SUCCESSFULLY!" | Out-File -FilePath $LogPath -Encoding utf8 -Append
}
catch {
    Write-Output "An error occurred: $_"
    "[$(Get-Date)] ERROR: $_" | Out-File -FilePath $LogPath -Encoding utf8 -Append
    exit 1
}

"===== CleanAndLoadData.ps1 finished at $(Get-Date) =====" | Out-File -FilePath $LogPath -Encoding utf8 -Append

Next the bat file executes the ReloadNAFDataAndRebuildELO stored procedure in SQL and pulls the information into the relevant tables. It truncates the existing tables, pulls the information from the CleanData versions of the files and uses the relevant format.xml files to insert them into the SQL tables. If there are errors this is recorded in a new file in the Error folder and the relevant table in SQL. I have a Power Automate flow that alerts me if a file is placed into that folder

USE [BloodBowl]
GO
/****** Object:  StoredProcedure [dbo].[ReloadNAFDataAndRebuildELO]    Script Date: 06/01/2026 13:04:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ReloadNAFDataAndRebuildELO]
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY

        -- ======================================
        -- Step 1: Clean old tables
        -- ======================================
        TRUNCATE TABLE CoachExport;
        TRUNCATE TABLE CoachRankingVariant;
        TRUNCATE TABLE Game;
        TRUNCATE TABLE Race;
        TRUNCATE TABLE StatsGroup;
        TRUNCATE TABLE StatsList;
        TRUNCATE TABLE Variants;
        TRUNCATE TABLE Tournament; -- Added

        -- ======================================
        -- Step 2: Bulk Insert cleaned CSVs
        -- ======================================

        -- CoachExport
        BULK INSERT CoachExport
        FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\CoachExport.csv'
        WITH (
            FORMATFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Formats\CoachExport_Format.xml',
            CODEPAGE = '65001',
            FIRSTROW = 2,
            ERRORFILE = 'C:\Users\david.ridley\OneDriveBB\Reports\ELO\Errors\Error_CoachExport'
        );

        -- CoachRankingVariant
        BULK INSERT CoachRankingVariant
        FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\naf_coachranking_variant.csv'
        WITH (
            FORMATFILE = 'C:\Users\david.ridley\OneDriveBB\Reports\ELO\Formats\CoachRankingVariant_Format.xml',
            CODEPAGE = '65001',
            FIRSTROW = 2,
            ERRORFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Errors\Error_CoachRankingVariant'
        );

        -- Game
BULK INSERT Game
FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\naf_game.csv'
WITH (
    FORMATFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Formats\Game_Format.xml',
    CODEPAGE = '65001',
    FIRSTROW = 2,
    ERRORFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Errors\Error_Game',
    MAXERRORS = 100
);

        -- Race
        BULK INSERT Race
        FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\naf_race.csv'
        WITH (
            FORMATFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Formats\Race_Format.xml',
            CODEPAGE = '65001',
            FIRSTROW = 2,
            ERRORFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Errors\Error_Race'
        );

        -- StatsGroup
        BULK INSERT StatsGroup
        FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\naf_tournament_statistics_group.csv'
        WITH (
            FORMATFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Formats\StatsGroup_Format.xml',
            CODEPAGE = '65001',
            FIRSTROW = 2,
            ERRORFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Errors\Error_StatsGroup'
        );

        -- StatsList
        BULK INSERT StatsList
        FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\naf_tournament_statistics_list.csv'
        WITH (
            FORMATFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Formats\StatsList_Format.xml',
            CODEPAGE = '65001',
            FIRSTROW = 2,
            ERRORFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Errors\Error_StatsList'
        );

        -- Variants
        BULK INSERT Variants
        FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\naf_variants.csv'
        WITH (
            FORMATFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Formats\Variants_Format.xml',
            CODEPAGE = '65001',
            FIRSTROW = 2,
            ERRORFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Errors\Error_Variants'
        );

        -- Tournament (new)
        BULK INSERT Tournament
        FROM 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\CleanData\naf_tournament.csv'
        WITH (
            FORMATFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Formats\Tournament_Format.xml',
            CODEPAGE = '65001',
            FIRSTROW = 2,
            ERRORFILE = 'C:\Users\david.ridley\OneDrive\BB\Reports\ELO\Errors\Error_Tournament'
        );

        -- ======================================
        -- Step 3: Rebuild Elo Tables
        -- ======================================

        INSERT INTO dbo.elo_automation_log (status, message)
        VALUES ('Success', 'Full reload completed successfully.');

    END TRY

    BEGIN CATCH
        INSERT INTO dbo.elo_automation_log (status, message)
        VALUES ('Failure', ERROR_MESSAGE());
    END CATCH
END

The next stored procedure called RebuildELO then truncates two tables the elo_game_log and elo_daily_summary. The game log creates a record for each player, for each game played and calculates an ELO score based on their result and opponent. The daily summary takes that table, filters by players nationality as we’re only worried about Welsh players and gives them an ELO rating for every single day. It’s updated for the days they’ve played and that result is carried over on days they don’t

USE [BloodBowl]
GO
/****** Object:  StoredProcedure [dbo].[RebuildELO]    Script Date: 06/01/2026 13:03:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RebuildELO]
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Step 1: Clean tables
        TRUNCATE TABLE elo_game_log;
        TRUNCATE TABLE elo_daily_summary;

        -- Step 2: Setup temp tracking tables
        IF OBJECT_ID('tempdb..#elo_tracker') IS NOT NULL DROP TABLE #elo_tracker;
        IF OBJECT_ID('tempdb..#tournament_k_values') IS NOT NULL DROP TABLE #tournament_k_values;

        CREATE TABLE #elo_tracker (
            [NAF Nr] INT PRIMARY KEY,
            [NAF name] NVARCHAR(255),
            elo FLOAT
        );

        INSERT INTO #elo_tracker ([NAF Nr], [NAF name], elo)
        SELECT [NAF Nr], [NAF name], 150.0
        FROM CoachExport;

        CREATE TABLE #tournament_k_values (
            tournamentid INT PRIMARY KEY,
            k_value FLOAT
        );

        INSERT INTO #tournament_k_values (tournamentid, k_value)
        SELECT
            g.tournamentid,
            SQRT(CAST(
                CASE 
                    WHEN COUNT(DISTINCT g.coachid) > 
                         CASE WHEN MAX(t.tournamentmajor) = 'yes' THEN 60 ELSE 32 END
                    THEN CASE WHEN MAX(t.tournamentmajor) = 'yes' THEN 60 ELSE 32 END
                    ELSE COUNT(DISTINCT g.coachid)
                END AS FLOAT
            )) * 2 AS k_value
        FROM (
            SELECT tournamentid, homecoachid AS coachid
            FROM Game
            WHERE naf_variantsid IN (1, 13, 15)
            UNION ALL
            SELECT tournamentid, awaycoachid AS coachid
            FROM Game
            WHERE naf_variantsid IN (1, 13, 15)
        ) g
        JOIN Tournament t ON g.tournamentid = t.tournamentid
        GROUP BY g.tournamentid;

        -- Step 3: Game loop to populate elo_game_log
        DECLARE @gameid INT, @gamedate DATE, @homeid INT, @awayid INT,
                @home_score INT, @away_score INT,
                @home_elo FLOAT, @away_elo FLOAT,
                @K FLOAT, @tournamentid INT,
                @exp_home FLOAT, @exp_away FLOAT,
                @result_home FLOAT, @result_away FLOAT,
                @new_home_elo FLOAT, @new_away_elo FLOAT;

        DECLARE game_cursor CURSOR FOR
        SELECT gameid, TRY_CAST([date] AS DATE), homecoachid, awaycoachid, goalshome, goalsaway, tournamentid
        FROM Game
        WHERE naf_variantsid IN (1, 13, 15)
        ORDER BY TRY_CAST([date] AS DATE), gameid;

        OPEN game_cursor;
        FETCH NEXT FROM game_cursor INTO @gameid, @gamedate, @homeid, @awayid, @home_score, @away_score, @tournamentid;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @home_elo = elo FROM #elo_tracker WHERE [NAF Nr] = @homeid;
            SELECT @away_elo = elo FROM #elo_tracker WHERE [NAF Nr] = @awayid;

            IF @home_elo IS NULL SET @home_elo = 150.0;
            IF @away_elo IS NULL SET @away_elo = 150.0;

            SELECT @K = k_value FROM #tournament_k_values WHERE tournamentid = @tournamentid;
            IF @K IS NULL SET @K = 11.0;

            SET @exp_home = 1.0 / (1 + POWER(10.0, (@away_elo - @home_elo) / 150.0));
            SET @exp_away = 1.0 / (1 + POWER(10.0, (@home_elo - @away_elo) / 150.0));

            IF @home_score > @away_score BEGIN SET @result_home = 1; SET @result_away = 0; END
            ELSE IF @home_score < @away_score BEGIN SET @result_home = 0; SET @result_away = 1; END
            ELSE BEGIN SET @result_home = 0.5; SET @result_away = 0.5; END

            SET @new_home_elo = @home_elo + @K * (@result_home - @exp_home);
            SET @new_away_elo = @away_elo + @K * (@result_away - @exp_away);

            UPDATE #elo_tracker SET elo = @new_home_elo WHERE [NAF Nr] = @homeid;
            UPDATE #elo_tracker SET elo = @new_away_elo WHERE [NAF Nr] = @awayid;

            INSERT INTO elo_game_log (gameid, [date], [NAF Nr], [NAF name], result, home_away, k_value, elo_before, expected_score, result_score, elo_after)
            SELECT @gameid, @gamedate, @homeid, ce.[NAF name],
                   CASE WHEN @result_home = 1 THEN 1 WHEN @result_home = 0.5 THEN 0.5 ELSE 0 END,
                   'Home', @K, @home_elo, @exp_home, @result_home, @new_home_elo
            FROM CoachExport ce WHERE ce.[NAF Nr] = @homeid;

            INSERT INTO elo_game_log (gameid, [date], [NAF Nr], [NAF name], result, home_away, k_value, elo_before, expected_score, result_score, elo_after)
            SELECT @gameid, @gamedate, @awayid, ce.[NAF name],
                   CASE WHEN @result_away = 1 THEN 1 WHEN @result_away = 0.5 THEN 0.5 ELSE 0 END,
                   'Away', @K, @away_elo, @exp_away, @result_away, @new_away_elo
            FROM CoachExport ce WHERE ce.[NAF Nr] = @awayid;

            FETCH NEXT FROM game_cursor INTO @gameid, @gamedate, @homeid, @awayid, @home_score, @away_score, @tournamentid;
        END

        CLOSE game_cursor;
        DEALLOCATE game_cursor;

        -- Step 4: Daily summary from 2023-01-01
        DECLARE @maxDate DATE;
        SELECT @maxDate = CAST(GETDATE() AS DATE); -- force to today

        ;WITH Calendar AS (
            SELECT CAST('2023-01-01' AS DATE) AS [date]
            UNION ALL
            SELECT DATEADD(DAY, 1, [date])
            FROM Calendar
            WHERE [date] < @maxDate
        ),
        WelshCoaches AS (
            SELECT DISTINCT [NAF Nr], [NAF name]
            FROM CoachExport
            WHERE Country = 'Wales'
        ),
        AllCoachDates AS (
            SELECT c.[NAF Nr], c.[NAF name], cal.[date]
            FROM WelshCoaches c
            CROSS JOIN Calendar cal
        ),
        LastGamePerDay AS (
            SELECT [NAF Nr], [date], MAX(gameid) AS last_gameid
            FROM elo_game_log
            GROUP BY [NAF Nr], [date]
        ),
        DailyELOs AS (
            SELECT g.[NAF Nr], g.[date], g.elo_after, g.gameid
            FROM elo_game_log g
            JOIN LastGamePerDay d ON g.[NAF Nr] = d.[NAF Nr] AND g.[date] = d.[date] AND g.gameid = d.last_gameid
        ),
        Enriched AS (
            SELECT a.[NAF Nr], a.[NAF name], a.[date], d.elo_after AS todays_elo,
                (
                    SELECT TOP 1 d2.elo_after
                    FROM DailyELOs d2
                    WHERE d2.[NAF Nr] = a.[NAF Nr] AND d2.[date] < a.[date]
                    ORDER BY d2.[date] DESC
                ) AS previous_elo,
                (
                    SELECT COUNT(*) FROM elo_game_log g
                    WHERE g.[NAF Nr] = a.[NAF Nr] AND g.[date] = a.[date]
                ) AS games_played
            FROM AllCoachDates a
            LEFT JOIN DailyELOs d ON a.[NAF Nr] = d.[NAF Nr] AND a.[date] = d.[date]
        )
        INSERT INTO elo_daily_summary ([NAF Nr], [NAF name], [date], elo_rating, games_played_today, elo_change)
        SELECT 
            [NAF Nr], [NAF name], [date],
            COALESCE(todays_elo, previous_elo, 150.0) AS elo_rating,
            games_played,
            CASE 
                WHEN todays_elo IS NOT NULL AND previous_elo IS NOT NULL THEN todays_elo - previous_elo
                WHEN todays_elo IS NOT NULL THEN todays_elo - 150.0
                ELSE 0 
            END AS elo_change
        FROM Enriched
        OPTION (MAXRECURSION 32767);

        INSERT INTO dbo.elo_automation_log (status, message)
        VALUES ('Success', 'ELO rebuild for Welsh coaches from Jan 1, 2023 completed.');

    END TRY
    BEGIN CATCH
        INSERT INTO dbo.elo_automation_log (status, message)
        VALUES ('Failure', ERROR_MESSAGE());
    END CATCH
END

With that done we need to import it into Power BI to be able to publish it. I have created a report and imported the following tables from the SQL Database. CoachExport, elo_daily_summary & elo_game_log. In truth only the Daily Summary is the only one we need but I use the others to sense check that. I have created new calculated tables in the report based on the daily summary to return results for the key dates in the charter and to work out the averages. This table is called ELO_Snapshot_2025 though will obviously be creating a 2026 version. I have Power BI on prem gateway personal installed on my machine and have a scheduled refresh of the report that happens at 10:00 every day and updates the prepublished report to the web

ELO_Snapshot_2025 = 
VAR TodayDate = TODAY()

VAR CoachesWith3Games2025 =
    CALCULATETABLE(
        VALUES (elo_daily_summary[NAF Nr]),
        FILTER (
            elo_daily_summary,
            YEAR (elo_daily_summary[date]) = 2025 &&
            elo_daily_summary[games_played_today] > 0
        )
    )

VAR BaseTable =
    SELECTCOLUMNS (
        CoachesWith3Games2025,
        "NAF Nr", elo_daily_summary[NAF Nr],
        "NAF name", CALCULATE (
            MAX (elo_daily_summary[NAF name])
        )
    )

VAR Result =
    ADDCOLUMNS (
        BaseTable,
        // ELO Snapshots
        "ELO Dec 2024", 
            CALCULATE (
                MAX (elo_daily_summary[ELO Rating]),
                elo_daily_summary[date] = DATE (2024, 12, 31)
            ),
        "ELO Mar 2025", 
            VAR CoachID1 = [NAF Nr]
            VAR TargetDate1 = DATE (2025, 3, 31)
            VAR LastGame1 = CALCULATE (
                MAX (elo_daily_summary[date]),
                elo_daily_summary[games_played_today] > 0,
                elo_daily_summary[NAF Nr] = CoachID1,
                elo_daily_summary[date] <= TargetDate1
            )
            RETURN IF (
                DATEDIFF (LastGame1, TargetDate1, DAY) > 365,
                150,
                CALCULATE (
                    MAX (elo_daily_summary[ELO Rating]),
                    elo_daily_summary[date] = TargetDate1,
                    elo_daily_summary[NAF Nr] = CoachID1
                )
            ),
        "ELO Jun 2025", 
            VAR CoachID2 = [NAF Nr]
            VAR TargetDate2 = DATE (2025, 6, 30)
            VAR LastGame2 = CALCULATE (
                MAX (elo_daily_summary[date]),
                elo_daily_summary[games_played_today] > 0,
                elo_daily_summary[NAF Nr] = CoachID2,
                elo_daily_summary[date] <= TargetDate2
            )
            RETURN IF (
                DATEDIFF (LastGame2, TargetDate2, DAY) > 365,
                150,
                CALCULATE (
                    MAX (elo_daily_summary[ELO Rating]),
                    elo_daily_summary[date] = TargetDate2,
                    elo_daily_summary[NAF Nr] = CoachID2
                )
            ),
        "ELO Sep 2025", 
            VAR CoachID3 = [NAF Nr]
            VAR TargetDate3 = DATE (2025, 9, 30)
            VAR LastGame3 = CALCULATE (
                MAX (elo_daily_summary[date]),
                elo_daily_summary[games_played_today] > 0,
                elo_daily_summary[NAF Nr] = CoachID3,
                elo_daily_summary[date] <= TargetDate3
            )
            RETURN IF (
                DATEDIFF (LastGame3, TargetDate3, DAY) > 365,
                150,
                CALCULATE (
                    MAX (elo_daily_summary[ELO Rating]),
                    elo_daily_summary[date] = TargetDate3,
                    elo_daily_summary[NAF Nr] = CoachID3
                )
            ),
        "ELO Dec 2025", 
            VAR CoachID4 = [NAF Nr]
            VAR TargetDate4 = DATE (2025, 12, 31)
            VAR LastGame4 = CALCULATE (
                MAX (elo_daily_summary[date]),
                elo_daily_summary[games_played_today] > 0,
                elo_daily_summary[NAF Nr] = CoachID4,
                elo_daily_summary[date] <= TargetDate4
            )
            RETURN IF (
                DATEDIFF (LastGame4, TargetDate4, DAY) > 365,
                150,
                CALCULATE (
                    MAX (elo_daily_summary[ELO Rating]),
                    elo_daily_summary[date] = TargetDate4,
                    elo_daily_summary[NAF Nr] = CoachID4
                )
            ),
        "ELO Today",
            VAR CoachID5 = [NAF Nr]
            VAR LastGameToday = CALCULATE (
                MAX (elo_daily_summary[date]),
                elo_daily_summary[games_played_today] > 0,
                elo_daily_summary[NAF Nr] = CoachID5,
                elo_daily_summary[date] <= TodayDate
            )
            RETURN IF (
                DATEDIFF (LastGameToday, TodayDate, DAY) > 365,
                150,
                CALCULATE (
                    MAX (elo_daily_summary[ELO Rating]),
                    elo_daily_summary[date] = TodayDate,
                    elo_daily_summary[NAF Nr] = CoachID5
                )
            )
    )

RETURN
    ADDCOLUMNS (
        Result,
        "Avg ELO 2025",
            VAR EloList =
                UNION (
                    ROW ( "ELO", [ELO Mar 2025] ),
                    ROW ( "ELO", [ELO Jun 2025] ),
                    ROW ( "ELO", [ELO Sep 2025] ),
                    ROW ( "ELO", [ELO Dec 2025] )
                )
            RETURN
                AVERAGEX ( EloList, [ELO] )
    )

I am a big nerd haha. For most of you this will just look like madness and to a certain degree I guess it is. For some though I hope it does make sense and is useful for you. If you have any questions please don’t hesitate to reach out to me. I’ll also be happy to share the files I use etc too.