Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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?

 

 

Schermafbeelding 2020-11-16 160214.png

6 REPLIES 6
Anonymous
Not applicable

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
Microsoft Employee
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
    ) + 1

 

 

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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
Super User
Super User

@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
Not applicable

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
Super User
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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors