Calculate dates between

[gclements]

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

 

[gclements · Accepted solution]

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

)

[BA_Pete]

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