cancel
Showing results 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

Frequent Visitor

## DATEDIFF with condition

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.

1 ACCEPTED SOLUTION
Super User

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``````
12 REPLIES 12
Super User

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``````
Frequent Visitor

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:

Super User

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

Frequent Visitor

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

I share my PBI file

Super User

Hi, @JavidRobatian
@JavidRobatian

in above you create a column

my code work with measure so try to mae measure

Frequent Visitor

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

Super User

HI, @JavidRobatian

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

Frequent Visitor

Yes.

Amazing its work.

Thank you so much sir.

Super User

@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] ) )
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

I tried this but I think it doesn't work properly.

Super User

@JavidRobatian

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

Hi

I share my PBI file

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors