cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors