Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
okikuswanda
Frequent Visitor

Calculate Remove Date Blank Value

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

2 ACCEPTED SOLUTIONS
stevedep
Memorable Member
Memorable Member

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

View solution in original post

themistoklis
Community Champion
Community Champion

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

 

 

View solution in original post

2 REPLIES 2
themistoklis
Community Champion
Community Champion

@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
Memorable Member
Memorable Member

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors