topic Calculate Remove Date Blank Value in DAX Commands and Tips

Calculate Remove Date Blank Value

okikuswanda — Tue, 06 Oct 2020 06:26:14 GMT

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 ...

Re: Calculate Remove Date Blank Value

stevedep — Tue, 06 Oct 2020 08:22:12 GMT

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)
))

Re: Calculate Remove Date Blank Value

themistoklis — Tue, 06 Oct 2020 09:26:40 GMT

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])) )