topic Re: DATEDIFF with condition in DAX Commands and Tips

DATEDIFF with condition

JavidRobatian — Thu, 30 Nov 2023 05:58:04 GMT

Hi experts, hope you all doing good.
I have two tables in Power BI called Date and Invoice that are related with (invoice[date] 1:* Date[Date])
Now I'm trying to Calculate DateDiff with below condition:
1- The user not use slicer, in this case:
DateDiff = date[invoice] - today()
2- The user select the month using the table:
For example if user select 11 in month slicer:
DateDiff = date[invoice] - 11/01/2023
thank you help me to Calculate DateDiff with up condition.

Re: DATEDIFF with condition

Fowmy — Thu, 30 Nov 2023 07:12:53 GMT

JavidRobatian

Try this measure: Make sure you add a Year slicer

Datediff = IF( ISFILTERED( 'Date'[Month] ) , INT( MAX( 'Date'[Date] ) - MAX( Invoice[Date] ) ), INT(TODAY() - MAX( Invoice[Date] ) ) )

Re: DATEDIFF with condition

JavidRobatian — Thu, 30 Nov 2023 07:06:10 GMT

Thank you for your reply
I tried this but I think it doesn't work properly.

Re: DATEDIFF with condition

Dangar332 — Thu, 30 Nov 2023 07:10:39 GMT

Hi, JavidRobatian

try below

Measure = var a = SELECTEDVALUE('date'[month]) var b = DATEDIFF(CALCULATE(min(invoice[date]),ALLEXCEPT(invoice,invoice[cu nu])),TODAY(),DAY) var c = DATEDIFF(CALCULATE(min(invoice[date]),ALLEXCEPT(invoice,invoice[cu nu])),DATE(2023,a,1),DAY) var d = IF(ISFILTERED('date'[month]),c,b) return d

Re: DATEDIFF with condition

JavidRobatian — Thu, 30 Nov 2023 07:46:45 GMT

Thank you for your reply.
I tried this but its partly works.
I changed Min to Max

its work well when I not use slicer.

but I want when I choose 10 (in month slicer) Datediff equal to:

and when I choose 11 (in month slicer) Datediff equal to:

Re: DATEDIFF with condition

Dangar332 — Thu, 30 Nov 2023 08:03:28 GMT

HI, JavidRobatian

in your previous post DATEDIFF between DATE column and User SELECT Date

you mention smallest number for each customer thats why i use min if you want latest date then you can use max()

with same formula select month(11) below result in image

with same formula select month(10) below result in image

in above output is some diff in output

beacuse i am take your previous post data

Re: DATEDIFF with condition

Fowmy — Thu, 30 Nov 2023 08:04:21 GMT

JavidRobatian

Share your PBI file using Google Drive and past the link here.

Re: DATEDIFF with condition

JavidRobatian — Thu, 30 Nov 2023 08:31:29 GMT

Yes and thank you for your reply.

I don't know why it works for me like this.

I share my PBI file

https://drive.google.com/file/d/1MJ1vWk2BShxE87eH7BM7QYkBX03p8zY3/view?usp=drive_link

Re: DATEDIFF with condition

JavidRobatian — Thu, 30 Nov 2023 08:35:29 GMT

Thank you for your reply

I share my PBI file

https://drive.google.com/file/d/1MJ1vWk2BShxE87eH7BM7QYkBX03p8zY3/view?usp=drive_link

Re: DATEDIFF with condition

Dangar332 — Thu, 30 Nov 2023 08:50:19 GMT

Hi, JavidRobatian
JavidRobatian

in above you create a column

my code work with measure so try to mae measure

download .pbix file click HERE

Re: DATEDIFF with condition

JavidRobatian — Thu, 30 Nov 2023 09:02:03 GMT

You are right
It was my fault
But again, I don't know why my results don't match yours

Re: DATEDIFF with condition

Dangar332 — Thu, 30 Nov 2023 09:18:47 GMT

HI, JavidRobatian

i think you use relationship bw tables so try to delete relationship

Re: DATEDIFF with condition

JavidRobatian — Thu, 30 Nov 2023 09:20:48 GMT

Yes.

Amazing its work.

Thank you so much sir.