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?

# 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

[v-jiewu-msft]

Hi @sanderdeweert ,

Based on the description, are you getting the correct results? If you have solved the problem, please consider Accept it as the solution to help the other members find it more quickly. If not, try creating multiple measures to check which step results are wrong.

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

Measure 2 = 
SUMX(
VALUES(Job),
CALCULATE(
SUM('factPrognosis'[PrognosisTotalRevenuesRoughAmount_RCY]),
FILTER(
ALL(factPrognosisLine),
factPrognosisLine[PrognosisDateID] = Measure
),
FILTER(
Job,
Job[PostingDateClosing] > max_date ||
Job[PostingDateClosing] < DATE(1900, 1, 2)
)
)
)

SUMX function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanderdeweert]

The solution is working but it is extremely slow. I don't know how to make it faster.

[v-jiewu-msft]

Hi,

If there is too much data in the table and the DAX formula is too complex, it may affect the calculation time.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saud968]

Potential Issues and Suggestions
Filtering on Date Dimension:

The use of ALL('Date') in multiple places might be causing the measure to ignore the current context of the Date dimension. This could lead to higher values than expected because it removes all filters on the Date table.
Calculation of _dateID:

The calculation of _dateID uses ALL('Date'), which removes any filters on the Date table. This might not be what you want if you need to respect the current context of the Date dimension.
SUMX Calculation:

The SUMX function iterates over the VALUES(Job), but the inner CALCULATE might be affected by the ALL('Date') and FILTER(ALL(factPrognosisLine),) statements, potentially leading to unexpected results.
Revised Measure
Here is a revised version of your measure that tries to address these issues:

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

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

RETURN
SUMX(
VALUES(Job),
CALCULATE(
SUM('factPrognosis'[PrognosisTotalRevenuesRoughAmount_RCY]),
FILTER(
ALL(factPrognosisLine),
factPrognosisLine[PrognosisDateID] = _dateID
),
FILTER(
Job,
Job[PostingDateClosing] > max_date ||
Job[PostingDateClosing] < DATE(1900, 1, 2)
)
)
)
Explanation
max_date: This variable captures the maximum date in the current context of the Date dimension.
_dateID: This variable calculates the latest PrognosisDateID where PrognosisFrozen is true and PrognosisDate is less than or equal to max_date, without removing the Date context.
SUMX Calculation: The SUMX function iterates over the VALUES(Job) and calculates the sum of PrognosisTotalRevenuesRoughAmount_RCY for each job, using the filtered factPrognosisLine and Job tables.

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

[sanderdeweert]

Thanks for your quik response!

The problem with this solution is dat I only get the Job rows where de prognosisDate is inside the filtered date context. An example: If I filter on june 2024, I want to get the values of the latest prognoses before the end of june 2024. In the example below it would be the prognosisDate 13-03-2024 (march 13th, 2024).

[saud968]

Let's adjust the measure to ensure it captures the latest prognosis date before the end of the filtered date context.

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('factPrognosis'[PrognosisTotalRevenuesRoughAmount_RCY]),
FILTER(
ALL(factPrognosisLine),
factPrognosisLine[PrognosisDateID] = _dateID
),
FILTER(
Job,
Job[PostingDateClosing] > max_date ||
Job[PostingDateClosing] < DATE(1900, 1, 2)
)
)
)

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

[sanderdeweert]

That still doesn't work. now the amount per job has some missing values. The total is still not right but the same as the orginal measure. 

I replaced the SUM(factprognosis) with factPrognosisLine[PrognosisTotalRevenues_PL]. This is the same in both measures. In Tabular editor it only sums 3 values from this fact.

[saud968]

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!

[sanderdeweert]

It's still not working. Now the values for some jobs are still missing (I think because there is no ALL(Date) in the filtering) and the values for the Jobs are to high

[sanderdeweert · Accepted solution]

Somehow it works when I put a SUMX in another measure =

# Prognose opbrengsten V3 =

SUMX(

    'Job',

    [# Prognose opbrengsten OP]

)

This is the original 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(

        factPrognosisLine[PrognosisTotalRevenues_PL],

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

        ALL('Date'),

        FILTER(

            Job,

            Job[PostingDateClosing] > max_date ||

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

        )

    )

)