SUMX no good CALCULATION TOTAL

[Bamak]

Hi

I want to display the cost of my projects according to the Max date selected in the slicer

Project name	Cost amount	Start Date
PRJ1	75	01/03/2019
PRJ1	89	05/03/2019
PRJ2	95	01/03/2019
PRJ2	99	05/03/2019
PRJ3	33	01/03/2019

But the total is incorrectly displayed in Power BI

This certainly comes from not taking into account the cost of the project 3
I guess my SUMX formula has an error

Max_SelectedDate = MAXX(ALLSELECTED(Feuil1[Start Date]); Feuil1[Start Date])

Max Date Cost = SUMX(FILTER(Feuil1;Feuil1[Start Date]=[Max_SelectedDate]);CALCULATE(SUM(Feuil1[Cost amount])))

Thanks a lot for help

Regards

[v-frfei-msft]

Hi @Bamak ,

Actually it is not an issue of the SUMX function. Here we should create another calculated table to work on it.

Table = VALUES(Feuil1[Start Date])

Afther to create the measures as below.

Max_SelectedDate = MAXX('Table','Table'[Start Date])

Measure = SUMX(FILTER(Feuil1,Feuil1[Start Date]=[Max_SelectedDate]),'Feuil1'[Cost amount])

Regards,

Frank

[Bamak · Accepted solution]

Hi

I found a solution

Manage a new colnmn 

MaxDateStart = IF(Feuil1[Start Date]=CALCULATE(Max('Feuil1'[Start Date]);ALLEXCEPT(Feuil1;Feuil1[Project name]));1;0)

Now ...   Max Date Cost3 = SUMX(FILTER(Feuil1;Feuil1[MaxDateStart]=1);CALCULATE(SUM(Feuil1[Cost amount])))

For me SUMX with filter run better (good total )  with a criterion in "hard" (not a variable)

Regards

[Bamak]

Thank you very much v-frfei-msft

But I think I did not speak well about my functional need

In fact if I select the 2 dates in the slicers, I would like to see the cost of the max dates present in the 2 dates range
Here we miss the project cost 3 (started on 1/3/2019)
And that with a correct final total
By cons everything is OK for project 1 & 2, the cost of 5/3/2019 is displayed

Regards

Christophe (powerBI france fan)