Forum Discussion
Calculate dates between
Hi, I have the below measure which calculates the number of days between two dates filtered by the datebetween function. I only want to show this measure when it falls between the dates provided in DateBetween. This works until I add the +1, then it provides a value against all dates (see image). How do I add 1 without breaking the filter?
MEASURE Charge[RentalDaysUsed] =
CALCULATE (
DATEDIFF ( [CurrentRentalFromDate], MAX ( Charge[dtChargeDate1] ), DAY ) + 1,
DATESBETWEEN (
Charge[dtChargeDate1],
[CurrentRentalFromDate],
MAX ( Charge[dtChargeDate1] )
)
)
Thansk for the reply but I fixed this by adding a FILTER instead of the DATESBETWEEN:
CALCULATE (DATEDIFF ( MAX ( Charge[Charge Date 1] ), [Current Rental To Date], DAY ),FILTER(Charge, Charge[Charge Date 1] >= [Current Rental From Date]))
2 Replies
Hi gclements ,
You could try something like this:
Charge[RentalDaysUsed] = VAR __daysUsed = CALCULATE( DATEDIFF( [CurrentRentalFromDate], MAX(Charge[dtChargeDate1]), DAY ), DATESBETWEEN( Charge[dtChargeDate1], [CurrentRentalFromDate], MAX(Charge[dtChargeDate1]) ) ) RETURN SWITCH( TRUE(), ISBLANK(__daysUsed), BLANK(), __daysUsed + 1 )Not the most elegant solution, but should get you over the line.
Pete
Thansk for the reply but I fixed this by adding a FILTER instead of the DATESBETWEEN:
CALCULATE (DATEDIFF ( MAX ( Charge[Charge Date 1] ), [Current Rental To Date], DAY ),FILTER(Charge, Charge[Charge Date 1] >= [Current Rental From Date]))
