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 between DATE column and User SELECT Date

Hello, I have a very complicated problem. Thank you for your help.
I have two tables in Power BI called Date and Invoice that are related
Now I want to calculate the DateDiff column in the invoice table as follows:
Using the date table and the month column, we create a date filter on the page.
There are two modes
1- The user has not selected the month, in this case:
DateDiff = date[invoice] - today()
Of course, this number must be the smallest number for each customer, for example, for customer number 10
11/28/2023-11/1/2023 =27
2- The user selects the date of the month using the table:
In this case, Today() is equal to the first day of the month selected by the user, and then the DateDiff calculation changes according to the user's selection.

1 ACCEPTED SOLUTION
Super User

hi, @JavidRobatian

try below code

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

hi, @JavidRobatian

try below code

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

If the explanation is not clear, please tell me so that I can explain more.

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.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors