Wrong outcome using DATEDIFF

[Anonymous]

In the screenshot below you can see my Power BI page. I want to calculate the days between two dates. I want to have the number of days from VALIDFROM to Tot Datum. But when the Tot Datum is not within the filter range I want it to calculate the date difference until the maximum date from the filter. In this example, I show all records in the period 1-1-2020 until 31-10-2020. The measures are calculated as follows:

- Test = MAX('Dim_Datum Periode'[Datum]), where 'Dim_Datum Periode'[Datum] is also in the filter

- Tijd in Leegstand = DATEDIFF('PMCCONTRACT'[VALIDFROM],[Test],DAY)+1

In the table below, the Test measure gives the right value for each record. However, when Tijd in Leegstand is calculated only the number of days is calculated in the right way when Test > Tot Datum, but not when Test < Tot Datum. For example, see the record underlined in red. Here, the Tijd in Leegstand should be 61 and not 77. This is strange to me because the measure Test is right in this row. How can I solve this problem?

[Anonymous]

Thanks for you reply. VALIDFROM is a column from the contracts table while Tijd in Leegstand is a measure. I modified your column somewhat by:

Tijd in Leegstand =

VAR maxdate =

        MAX ( 'Dim_Datum Periode'[Datum] )

VAR totdatumdate =

        PMCCONTRACT[Tot Datum]

VAR comparedate =

        IF (

        maxdate < totdatumdate, maxdate, totdatumdate)

RETURN

        DATEDIFF (

        PMCCONTRACT[VALIDFROM],

        comparedate,

        DAY) + 1

According to my logic this should give the right result but it still only gives the correct values when maxdate > totdatumdate but not when maxdate < totdatumdate. This is because I want maxdate to depend on the slicer Periode you see in my first post. Maxdate should now be 31-10-2020 but when I change the slicer to, for example 12-11-2020, maxdate should change too. I understood that this cannot be done using a column as they are not dynamic so is there a way to create what I want using a measure?

[mahoneypat]

For your syntax to work, either VALIDFROM is a measure or Tijd in Leedstand is a DAX column.  I suspect the later.  Your columns probably should be done as measures instead.  But for the one you are trying to respond to the slicer, you could use this measure expression

Tijd in Leegstand =
VAR maxdate =
    MAX ( 'Dim_Datum Periode'[Datum] )
VAR totdatumdate =
    MAX ( 'PMContract'[Tot Datum] )
VAR comparedate =
    IF (
        maxdate > totdatumdate,
        totdatumdate,
        maxdate
    )
RETURN
    DATEDIFF (
        MAX ( 'PMContract'[VALIDFROM] ),
        comparedate,
        DAY
    ) + 1

Regards,

Pat

[Anonymous]

I understand that. However, using my measure Tijd in Leegstand does not display the correct values as I showed in my initial post. How can I adjust my measure such that it shows me the correct values in the table?

[amitchandak]

@Anonymous , A column can not use slicer value. The measure can , So think in term of measure. You might have to force a row context if needed

[Anonymous]

Alright. And there is no way of doing what I want by using DATEDIFF in the contracts table using a date column that changes dynamically with the slicer?

[amitchandak]

@Anonymous , this seems like a column. You can not use slicer for column

Create a measure like

Tijd in Leegstand = DATEDIFF(max('PMCCONTRACT'[VALIDFROM]),[Test],DAY)+1