DAX SUMX total not working

Let's try another approach to ensure we are correctly summing the values for each job.

Revised Measure
Prognose opbrengsten OP =
VAR max_date = MAX('Date'[Date])

VAR _dateID =
CALCULATE(
MAX(factPrognosisLine[PrognosisDateID]),
FILTER(
ALL(Prognosis),
Prognosis[PrognosisFrozen] = "true" &&
Prognosis[PrognosisDate] <= max_date
)
)

RETURN
SUMX(
VALUES(Job),
CALCULATE(
SUM(factPrognosisLine[PrognosisTotalRevenues_PL]),
FILTER(
ALL(factPrognosisLine),
factPrognosisLine[PrognosisDateID] = _dateID
),
FILTER(
ALL(Job),
Job[PostingDateClosing] > max_date ||
Job[PostingDateClosing] < DATE(1900, 1, 2)
)
)
)
Explanation
max_date: Captures the maximum date in the current context of the Date dimension.
_dateID: Calculates the latest PrognosisDateID where PrognosisFrozen is true and PrognosisDate is less than or equal to max_date, using ALL(Prognosis) to ensure all prognosis dates are considered.
SUMX Calculation: Iterates over the VALUES(Job) and calculates the sum of PrognosisTotalRevenues_PL for each job, using the filtered factPrognosisLine and Job tables.
Key Changes
ALL(Job): Ensures that the filter on the Job table is correctly applied within the CALCULATE function.
factPrognosisLine[PrognosisTotalRevenues_PL]: Uses the specified column for summing the values.
Debugging Tips
Check Filter Context: Ensure that the filter context is correctly applied by using ALL to remove filters where necessary and VALUES to iterate over the correct context.
Intermediate Results: Create intermediate measures to check the values of max_date, _dateID, and the results of the SUMX calculation to debug where the issue might be occurring.
Try this revised measure and see if it resolves the issue.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!