Forum Discussion
Measure using DATESBETWEEN wrong total but correct row value
Super User
You can try below measure:
MeasureName =
SUMX(VALUES(PropertyInfoTable[Name]),CALCULATE(
VAR stdt =
IF (
SELECTEDVALUE ( DateTable[YearStartDate] )
<= SELECTEDVALUE ( PropertyInfoTable[PurchaseDate] ),
SELECTEDVALUE ( DateTable[YearStartDate]),
SELECTEDVALUE ( DateTable[YearStartDate])
)
VAR endt =
IF (
SELECTEDVALUE ( PropertyInfoTable[SellDate]) = BLANK (),
SELECTEDVALUE ( DateTable[Date] ),
SELECTEDVALUE ( PropertyInfoTable[SellDate])
)
RETURN
SUMX (
SUMMARIZE (
PropertyInfoTable,
PropertyInfoTable[Name]
),
CALCULATE (
SUM ( ActualCostsTable[Amount] ),
DATESBETWEEN ( DateTable[Date], stdt, endt )
)
)
)
)
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
I was able to create a measure containing the initial start date and end date variables/parameter in an measure called "TotalActualCostsTableAmount" and then put it inside the following measure .
SUMX (
SUMMARIZE (
PropertyInfoTable,
PropertyInfoTable[Name]
),
CALCULATE (
[TotalActualCostsTableAmt],
DATESYTD ( 'DateTable'[Date] )
)
)
My question is, are there any other more efficient ways of writing this measure that doesn't require SUMX(Summarize. I'm still confused as to why if don't wrap in a SUMX(Summarize and I select a single property the row and total are correct but if I select two or more properties the total get the date logic wrong.
