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
LLJ1221
Helper I
Helper I

have the difference between 2 dates dynamically

Hello,

 

I don't know if my problem has already been asked on the forum but I haven't found any answers.

 

I have the following table:

Transaction

Operation number

Operation number_1

date operation

1223

080

080

11/02/2023

1223

070

070

05/03/2023

1223

010

010

20/11/2022

1223

0200

0200

01/08/2022

 

The user can choose different operations through two filters (Operation number and Operation number_1) in order to obtain the difference in days between the 2 selected operations.

 

For example if the user filters on operation number = 080 and another filters on operation number_1 = 010, the calculation would be 02/11/2023 - 11/20/2022

 

How can I carry out this calculation using a Dax formula please?

 

Thanks

1 ACCEPTED SOLUTION

Are you able to simply replace the FIRSTDATE() function above with MIN(). That should return the same result, provided there are no blanks.

View solution in original post

3 REPLIES 3
vicky_
Super User
Super User

Please try the following: 

dates selected = 
var OPNo = CALCULATE(FIRSTDATE('Table'[date operation]), ALLEXCEPT('Table', 'Table'[Operation number]))
var OPNo1 = CALCULATE(FIRSTDATE('Table'[date operation]), ALLEXCEPT('Table', 'Table'[Operation number_1]))
return DATEDIFF(OPNo, OPNo1, DAY)

Hi @vicky_ ,

 

Thanks for your feedback.

 

I published to specify sue I susu in Direct Query mode and I know that I had already had problems with the "FirstDate" function because it was not supported in direct query mode

Are you able to simply replace the FIRSTDATE() function above with MIN(). That should return the same result, provided there are no blanks.

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.