Determining best or worst project

Dear Power BI community,

First of all i want to wish you all the best in 2026 and thank you for possibility attempting to help resolve my issue.

For a dashboard, i am trying to determine the best and worst project in a selected period. The dashboard shows various other data and has everything displayed twice, once to shown on a week level and once on a year level.

The determination of the best / worst project is done with the following dax formula:

Beste Project Nummer = 
VAR SelectedDates = 
    VALUES ( Datum[Datum] ) 
    
VAR Projects = 
    FILTER( 
            SUMMARIZE( 
                Opdracht, 
                Opdracht[DimOpdrachtId], 
                Opdracht[Projectnummer], 
                Opdracht[Korte tekst], 
                Opdracht[Opleveringsdatum], 
                "BrutomargePct", 
                CALCULATE( 
                    [Brutomarge %], 
                    TREATAS( 
                        SelectedDates, Opdracht[Opleveringsdatum] ), 
                        USERELATIONSHIP(Opdracht[Opleveringsdatum], Datum[Datum]) 
                ), 
                "Omzet", 
                CALCULATE( 
                    SUM(Opbrengsten[zBedrag]), 
                    TREATAS( SelectedDates, Opdracht[Opleveringsdatum] 
                    ), 
                    USERELATIONSHIP(Opdracht[Opleveringsdatum], Datum[Datum]) 
                ) 
            ), 
        NOT ISBLANK ( Opdracht[Opleveringsdatum] ) 
        && [Omzet] >= 1000 -- later finetunen 
    ) 
VAR Result =
    MAXX( TOPN(1, Projects, [BrutomargePct], DESC), Opdracht[Projectnummer] )

RETURN
    IF ( ISBLANK ( Result ), "Geen afgesloten projecten", Result )

*determination of worst project is the same but uses ASC instead of DESC in the Result Variable.

In this measure, we determine the best project within the selecteddates (according to date filter in the page using the date table) by calculating the gross margin (brutomargePct) in percentage. This comes from the measure [Brutomarge %] which simply divides two other measures, [Brutomarge] (gross margin) and [Project omzet] (project revenue):

Brutomarge % = 
DIVIDE(
    [Brutomarge],
    [Project Omzet]
)

Brutomarge = 
CALCULATE(
    SUM ( Opbrengsten[zBedrag] ) - SUM ( Kosten[zBedrag] )
)

Project Omzet = 
CALCULATE(
    SUM ( Opbrengsten[zBedrag] )
)

The problem i am facing is as follows:

To determine which projects to use in this summary of projects, the project should be ended within the selected week or year. The project end date comes from Opdracht[Opleveringsdatum]. However, on the dashboard the user filters using the date table (Datum[Datum]). The Date table and Opdracht table have a active relationship, but this is connected to another column and can not have a active relationship to the 'Opleveringsdatum' column because it would break many other things.

When i test in a test dashboarding and filter directly with the 'Opleveringsdatum' instead of using the Date table in the slicer, the result and calculations are correct. However when i move it to the live dashboard where it filters using the date table, none of the values are correct anymore. The Project Revenue, The Gross Margin and Gross Margin % are all correct, causing a incorrectly chosing best/worst project. It does mostly display the correct projects however, so almost always are the actual project numbers correct in both cases.

I tried solving this using the USERELATIONSHIP or TREATAS within the measures, but none of this has worked.

I am very curious to hear your thoughts!

Thanks again,

Olivier