Forum Discussion
Wrong outcome using DATEDIFF
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?
6 Replies
- amitchandak
Super User
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
- 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
- mahoneypat
Microsoft Employee
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
) + 1Regards,
Pat
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)RETURNDATEDIFF (PMCCONTRACT[VALIDFROM],comparedate,DAY) + 1According 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?
