sum filter not working correctly

[PowerKoen]

Hi there, need some help with my formula. I want to extract (based on selectedvalue of slicer) the LATEST date AND LATEST uploadtime and sum the values of those items

Current situation:

AssetID DealID Marketvalue Effectivedate Uploadtime Enddate Type
1 1 10 01/01/2019 02/01/2019 09:00 office
2 1 10 01/01/2019 02/01/2019 09:00 house
1 1 15 01/01/2019 05/01/2019 09:00 office
1 1 20 01/02/2019 02/02/2019 09:00 office
2 1 20 01/02/2019 02/02/2019 09:00 house
1 1 25 01/02/2019 05/02/2019 09:00 office
1 1 30 01/02/2019 05/02/2019 10:00 01/03/2019 10:00 office

My formula's

Market value totaal = 

VAR Year = YEAR(SELECTEDVALUE(Assets[Effectivedate]))
VAR Month = MONTH(SELECTEDVALUE(Assets[Effectivedate]))
VAR Day = DAY(SELECTEDVALUE(Assets[Effectivedate]))
Var Selectedday = DATE (Year; Month; Day)

var effectiveLAST = 

CALCULATE(  MAX(Assets[EffectiveDate]); FILTER ( allexcept(Assets; Assets[AssetID]; Assets[DealID]); Assets[EffectiveDate] <= Selectedday))

VAR uploadLAST = 

CALCULATE(  MAX(Assets[Uploadtime]); FILTER ( allexcept(Assets; Assets[AssetID]; Assets[DealID]); Assets[EffectiveDate] <= Selectedday))

var som = CALCULATE( SUM( Assets[Marketvalue]) ; Assets[Effectivedate] = effectivelaatste; Assets[Uploadtime] = uploadlaatste )

Return

CALCULATE( SUM( Assets[Marketvalue]);  Assets[Effectivedate] = effectiveLAST; Assets[Uploadtime] = uploadLAST)

This gives me the correct values behind the rows, but not the total / graph for each type.

I tried allexcept (assets; type) and summarize but not working.

Can anyone help me?

Gladly here from you. 

[dax · Accepted solution]

Hi PowerKoen, 

It seems that you want to get grant total based on current result, right? If so, you could create measure like below, then use this in table or chart

Measure 2 = IF(HASONEVALUE(Assets[AssetID]), [Market value totaal], SUMX(ALLSELECTED(Assets),[Market value totaal]))

Best Regards,
Zoe Zhi

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