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

New Member

## How to calculate date difference between earliest date field and normal date field

Dear Power BI community,

I am trying to solve how you can calculate the # of days between 2 dates when 1 column has duplicates and I want to select the earliest date from column 1 (Moment warehouse) + the date from column 'Delivery status' to calculate the # of days betweent hem when the Status = '2'.

This is how my data looks like (excel example from PBI) + what I would like (see last column in the screenshot):

I tried several options with the datediff functions but somehow I can't fix it and I would be really happy if someone can help me out with this one. I tried this formula:

Test=
VAR x =
MIN(
FILTER(ALL('Table1),'Table1'[Delivery-status].[Date] = min('Table1'[Delivery-status].[Date]) && 'Table1'[Status] = "2"),
'Table1'[Delivery-status].[Date]
)
VAR y = 'Table1'[Moment warehouse].[Date]
RETURN
DIVIDE(datediff(x,y,DAY),1)

Thank you guys in advance! Much appreciated.
1 ACCEPTED SOLUTION
Super User

Here you go

``````Test =
VAR CurrentNumberTable =
CALCULATETABLE (
'Table1',
ALLEXCEPT ( 'Table1', 'Table1'[Number], 'Table1'[Status] )
)
VAR MinWarehouseDate =
MINX ( CurrentNumberTable, 'Table1'[Moment warehouse] )
VAR MinDeliveryDate =
MINX ( CurrentNumberTable, 'Table1'[Delivery-status] )
RETURN
DATEDIFF ( MinDeliveryDate, MinWarehouseDate, DAY )``````
6 REPLIES 6
Super User

You mentioned "when the Status = '2'. " But you are showing a value at status 4?

New Member

You're right - I corrected it - only values when the status = '2'.

@tamerj1 , do you have a clue how I can fix this DAX formula?

Super User

``````Test =
VAR CurrentNumberTable =
CALCULATETABLE ( 'Table1', ALLEXCEPT ( 'Table1', 'Table1'[Number] ) )
VAR FilteredTable =
FILTER ( CurrentNumberTable, 'Table1'[Status] = 2 )
VAR MinWarehouseDate =
MINX ( FilteredTable, 'Table1'[Moment warehouse] )
VAR MinDeliveryDate =
MINX ( FilteredTable, 'Table1'[Delivery-status] )
RETURN
DATEDIFF ( MinDeliveryDate, MinWarehouseDate, DAY )``````
New Member

@tamerj1 , the formula works except if the status <> "2"(string field). Do you know how that's solved? Because when the status is not "2" , I would like to see nothing (blank/ "")

Super User

Here you go

``````Test =
VAR CurrentNumberTable =
CALCULATETABLE (
'Table1',
ALLEXCEPT ( 'Table1', 'Table1'[Number], 'Table1'[Status] )
)
VAR MinWarehouseDate =
MINX ( CurrentNumberTable, 'Table1'[Moment warehouse] )
VAR MinDeliveryDate =
MINX ( CurrentNumberTable, 'Table1'[Delivery-status] )
RETURN
DATEDIFF ( MinDeliveryDate, MinWarehouseDate, DAY )``````
New Member

Thank you so much @tamerj1 , I tried row per row and I completely understand the formula now. Have a great day!

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.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors