DAX SUMX total not working

[sanderdeweert]

This is my measure. I want to be able to filter on the Date dimension. Next I want to get back the latest date before the MAX date of the filted date dimension. This date is used to find the factprognosisLine values I want. This needs to be calculated for every line in the Job dimension. After that I want to sum the values. I use SUMX for this. unfortunately this is not working as I want. The value I get is to high. It seems to be a bug, but mayby I'm making a mistake.

Can someone help me?

This is my measure

# Prognose opbrengsten OP =

VAR max_date = MAX('Date'[Date])

VAR _dateID =

CALCULATE(

MAX(factPrognosisLine[PrognosisDateID]),

FILTER(

Prognosis,

Prognosis[PrognosisFrozen] = "true" &&

Prognosis[PrognosisDate] <= MAX('Date'[Date])

),

ALL('Date')

)

RETURN

SUMX(

VALUES(Job),

CALCULATE(

SUM ( 'factPrognosis'[PrognosisTotalRevenuesRoughAmount_RCY] ),

FILTER(ALL(factPrognosisLine), factPrognosisLine[PrognosisDateID] = _dateID),

ALL('Date'),

FILTER(

Job,

Job[PostingDateClosing] > max_date ||

Job[PostingDateClosing] < DATE(1900, 1, 2)

)

)

)

This is an example. This is the full table.

This are the relationships

[saud968]

You should post this on the link - https://community.fabric.microsoft.com/t5/Desktop/bd-p/power-bi-designer to get quick response. 

[v-tianyich-msft]

Hi @sanderdeweert ,

The expression uses all() multiple times, and there are multiple relationships, so it is possible to iterate over duplicate data multiple times. I suggest a step-by-step implementation, where a “total measure” is computed first, and then filtered so that the measure doesn't belong to any one table, which might solve the problem.

Best regards,
Community Support Team_ Scott Chang