DATEDIFF Hlep

[michael_knight]

Hi,

I'm trying to create a measure that inlcudes DateDiff

Option Agreed(WBAH) - Count = COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Agreed" && 
Opportunity[Company] = "Company 1" &&
Opportunity[Owner] <> "Person1" && 
Opportunity[Owner] <> "Person2" && 
Opportunity[Owner] <> "Person3" &&
IF(Opportunity[Date Diff - Agreed & Withdrawal Date] < 30, -1,0)))

I currently have a column that shows the difference in DAYS between two date columns. I want the line of code to not include any Agreed values if the Agreed & Withdrawal Date is less than 30

Does anyone know how I can change to code so it does this?

Thanks,

Mike

[amitchandak]

For anything like this row context is very important. So you need to use values or summarize

Option Agreed(WBAH) - Count = calculate(COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Agreed" && 
Opportunity[Company] = "Company 1" &&
Opportunity[Owner] <> "Person1" && 
Opportunity[Owner] <> "Person2" && 
Opportunity[Owner] <> "Person3" &&
IF(max(Opportunity[Date Diff - Agreed & Withdrawal Date] < 30, true(),false())))),values(Opportunity[ID]))

Refer to my blog how to make date diff work -https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[edhans]

You cannot use a field like that in the IF statement. Opportunity[Date Diff - Agreed & Withdrawal Date]is returning an entire column of dates. You can use it in the FILTER() function because it is iterating the rows. You need to convert that date to a scalar (single) value. Try:

MAX(Opportunity[Date Diff - Agreed & Withdrawal Date])

That will only work though if the measure is being used in a visual that is at the most detailed level. If it is at a summary level then it will only return the maximum value of that range of dates, which may not return what you want.

Also, if you are wanting to just use the DATEDIFF() function, it too requires scalar values, so:

DATEDIFF(
   MAX(Table[Date]),
   MAX(Table[Date2]),
   DAY)

Full documentation is here. 

[michael_knight]

Hi,

Cheers for the reply @edhans 

The column isn't a colum of dates, its a column of values that is the difference between Agreed Date and Withdrawal Date 

This part of the code...

Option Agreed(WBAH) - Count = COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Agreed" && 
Opportunity[Company] = "Company 1" &&
Opportunity[Owner] <> "Person1" && 
Opportunity[Owner] <> "Person2" && 
Opportunity[Owner] <> "Person3" &&

 Is perfectly fine and I'm happy with it. 

I want to include a certain formula that -1 if the Date Diff - Agreed & Withdrawal Date is less than 30 days.

If the Date Diff - Agreed & Withdrawal Date is greater than or if it's blank than don't do anything 

Cheers

[edhans]

Then you aren't useing DATEDIFF, so my IF(MAX(Table[columname)>30,true,false) holds.

[michael_knight]

Sorry, I'm still learning. 

I tried using IF(MAX(Table[columname)>30,true,false)and it doesn't work in the measure I created.

Option Agreed(WBAH) - Count = COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Agreed" && 
Opportunity[Company] = "Company 1" &&
Opportunity[Owner] <> "Person1" && 
Opportunity[Owner] <> "Person2" && 
Opportunity[Owner] <> "Person3" &&
IF(MAX(Opportunity[Date Diff - Agreed & Withdrawal Date] > 30), -1,0) ))

The error I'm getting is "The MAX function only accepts a column reference as an argument"

I put the ) after 30 because it wouldn't let me do the true, false otherwise 

[edhans]

your parenthesis is in the wrong place

IF(MAX(Opportunity[Date Diff - Agreed & Withdrawal Date] > 30), -1,0) ))<--Wrong
IF(MAX(Opportunity[Date Diff - Agreed & Withdrawal Date]) > 30, -1,0) ))<--Correct

The MAX() will return a single (scalar) value, and that will be compared to the 30, then return -1 or 0

[michael_knight]

Cheers, @edhans 

Gave that a go and the measure doesn't have any errors, however I'm not getting the result that I had anticipated. There are 11 records, one of them is 26 in the Agreed & Withdrawal Date so that shouldn't be included after we added that new line of code, making the total we want to be 10

As you can see in the 2nd image above, I am still getting 11 even with that new line we put in the measure

Thank you

[edhans]

Without your full model and detailed table views, preferably a PBIX file, we cannot help. Just an image of “26” in a column doesn’t help with any filter or row context happening.

The measure I provided did fix the issue. You just need to now understand why it is providing the result that it is, and that is how the model is being filtered.

[michael_knight]

I'll add the PBIX file now. I am 100% confident that the measure isn't doing what it is intented to do!

There are 11 record in this instance. The 26 is the number of days between Agreed Date and Withdrawal Date. And that record should not be include in the grand total. Apologies if that wasn't clear enough for you.

https://www.dropbox.com/s/pswxuno8xqmwa49/Acquisitions%20-%20Test.pbix?dl=0

[v-shex-msft]

HI @michael_knight,

I'd like to suggest you add formula in filter conditions with datediff function calculation result and use it as filter condition:

Option Agreed(WBAH) - Count =
COUNTROWS (
    FILTER (
        Opportunity,
        Opportunity[statuscode_display] = "Agreed"
            && Opportunity[Company] = "Company 1"
            && NOT ( Opportunity[Owner] IN { "Person1", "Person2", "Person3" } )
            && DATEDIFF ( Opportunity[Agreed], Opportunity[Withdrawal Date], DAY ) < 30
    )
)

Regards,

Xiaoxin Sheng

[michael_knight]

Hi @v-shex-msft 

Thanks for the reply, much appriciated. I think there is a problem with the way the formula is working. I believe the measure is filtering out EVERY record that is less than 30. A majoritiy of the records are not withdrawn so don't have a value in the DATEDIFF function. Essentially counting null as 0 and filtering out that record.

Does that make sense?

[edhans]

That is what it is doing. Null is zero for this purpose. I'm not clear on exactly what it is you are trying to do. I have your PBIX file but I cannot follow along with the examples you are providing. Nothing is matching up with your screenshots.

[michael_knight]

Hi @edhans 

I'll add another PBIX file, this time a lot more easier to follow.

https://www.dropbox.com/s/aievdh0e1zeum7u/Example.pbix?dl=0

The measure that you suggested does NOT filter out any DateDiff less than 30. 

There are 17 records in the image, and the Measure that you created displays 17. 

The two visuals above are using the measure you gave me. The one of the left is the actual value. The one on the right is me manually filtering out the DateDiff values that are lower than 30. 

Hope that makes it easier

Kind regards,

Mike

[edhans · Accepted solution]

That makes a HUGE difference. Data is the key. This will work - it returns 15.

Option Agreed =
COUNTROWS (
    FILTER (
        Sheet1,
        Sheet1[Agreed] = "Agreed"
            && Sheet1[Company] = "Company 1"
            && Sheet1[Owner] <> "Person12"
            && Sheet1[Owner] <> "Person11"
            && (
                Sheet1[Date Diff] > 30
                    || ISBLANK ( Sheet1[Date Diff] )
            )
    )
)

You don't need MAX inside of a FILTER function because FILTER is an iterator, and you didn't have that in the first example. WIthout the data and how it was being used I wasn't able to see what you were trying to do.

You also have to tell it that while you want it > 30, you want to also include blanks, otherwise this measure returns 1 record as everything but one is less than 30, or blank, and blank = 0 for DAX in this case.

Hope that helps. Thanks again for the data and PIBX. It makes a world of difference in trying to troubleshoot. 👍

[michael_knight]

Perfect,

Thank you very much @edhans, I very much appreciate it 

Cheers,

Mike

[edhans]

Excellent. Glad it helped and your project is moving forward.