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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JavidRobatian
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.Screenshot 2023-11-30 092551.pngScreenshot 2023-11-30 092633.png

1 ACCEPTED SOLUTION
Dangar332
Memorable Member
Memorable Member

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

View solution in original post

12 REPLIES 12
Dangar332
Memorable Member
Memorable Member

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

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

Screenshot 2023-11-30 111134.png

its work well when I not use slicer.

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

Screenshot 2023-11-30 111214.png

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

Screenshot 2023-11-30 111250.png

 

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 

 

Dangar332_0-1701331256907.png

 

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

Dangar332_1-1701331314538.png

 

in above output is some diff in output

beacuse i am take your previous post data

Yes and thank you for your reply.

I don't know why it works for me like this.Screenshot 2023-11-30 114738.pngScreenshot 2023-11-30 114750.png

I share my PBI file

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

Hi, @JavidRobatian 
@JavidRobatian 

in above you create a column 

my code work with measure so try to mae measure

download .pbix file click HERE 


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

Screenshot 2023-11-30 122902.pngScreenshot 2023-11-30 122910.png

HI, @JavidRobatian 

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

Yes.

Amazing its work.

Thank you so much sir.

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Screenshot 2023-11-30 103401.pngScreenshot 2023-11-30 103450.png

@JavidRobatian 

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

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors