Calculate Remove Date Blank Value

[okikuswanda]

Hi, i have dax measure for calculated column like this 

RealisasiEkvUSDCumTest:=
VAR MaxDate = CALCULATE(MAX(DimDate[Date]) , RELATEDTABLE(FactInvestment))
RETURN
CALCULATE (
SUM ( FactInvestment[RealisasiEkvUSD]),
FILTER (
ALLSELECTED(DimDate),
DimDate[Date] <= MaxDate)
)

but, i want remove that month have no value like image below (blank value is Oct-Dec), Thanks ... 

[stevedep · Accepted solution]

Hi,

See below;

RealisasiEkvUSDCumTest:=
VAR MaxDate = CALCULATE(MAX(DimDate[Date]) , RELATEDTABLE(FactInvestment))
RETURN

If (is blank(sum(FactInvestment[RealisasiEkvUSD]) ) = true(), blank(),
CALCULATE (
SUM ( FactInvestment[RealisasiEkvUSD]),
FILTER (
ALLSELECTED(DimDate),
DimDate[Date] <= MaxDate)
))

[themistoklis · Accepted solution]

@okikuswanda 

You can try the following formula

RealisasiEkvUSDCumTest:=
VAR _MaxDate = CALCULATE(MAX(DimDate[Date]) , RELATEDTABLE(FactInvestment))
RETURN

IF( SELECTEDVALUE(DimDate[Date])<=_MaxDate ,
CALCULATE (
SUM ( FactInvestment[RealisasiEkvUSD]),
DATESYTD(DimDate[Date]))
)

[stevedep · Accepted solution]

Hi,

See below;

RealisasiEkvUSDCumTest:=
VAR MaxDate = CALCULATE(MAX(DimDate[Date]) , RELATEDTABLE(FactInvestment))
RETURN

If (is blank(sum(FactInvestment[RealisasiEkvUSD]) ) = true(), blank(),
CALCULATE (
SUM ( FactInvestment[RealisasiEkvUSD]),
FILTER (
ALLSELECTED(DimDate),
DimDate[Date] <= MaxDate)
))