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

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
Memorable Member

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
Memorable Member

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors