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

## Days Difference between two dates in same column with duplicate dates in Power BI

I have a column with duplicate dates. I need to calculate date difference based on another column.

For example, I have Stage column with 1,2,3... repeated values . What will be function I can use to calculate date values differences for stage 1?

2 ACCEPTED SOLUTIONS
Super User

maybe you can try this

measure=calculate(max(lastdate),allexcept(table,bomno))-calculate(min(lastdate),allexcept(table,bomno))

Proud to be a Super User!

Frequent Visitor

Thanks a lot. It worked for me...😊

18 REPLIES 18
Frequent Visitor

Hi all,

please help me. I need to calculate the sum of several date time differences by different values (Case ID status code changes and it has date and time). It is complicated to explain, so please look there:

Can anyone help? thanks in advance.

Super User

Hi,

This should ideally be written as a calculated column formula (not as a measure).  If you agree with my approach, then please share data in a format that can be pasted in an MS Excel file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor
 ID Received date and time New Value Date and time. 18794 2024-05-06 14:19 PAKLAUSIMAS 5-7-2024 9:01:59 AM 18794 2024-05-06 14:19 CAD 5-9-2024 11:37:04 AM 18794 2024-05-06 14:19 SĄMATOS RUOŠIMAS 5-9-2024 11:56:39 AM 18794 2024-05-06 14:19 CAD 5-9-2024 11:58:05 AM 18794 2024-05-06 14:19 SĄMATOS RUOŠIMAS 5-9-2024 1:01:58 PM 18794 2024-05-06 14:19 MARŽOS NUSTATYMAS 5-16-2024 4:56:39 PM 18794 2024-05-06 14:19 PASIŪLYMO RUOŠIMAS 5-16-2024 5:06:06 PM 18794 2024-05-06 14:19 PASIŪLYMAS IŠSIŲSTAS 5-20-2024 11:35:32 AM 18743 2024-05-02 14:08 PAKLAUSIMAS 5-2-2024 3:12:14 PM 18743 2024-05-02 14:08 CAD 5-2-2024 5:08:48 PM 18743 2024-05-02 14:08 SĄMATOS RUOŠIMAS 5-3-2024 9:34:08 AM 18743 2024-05-02 14:08 PASIŪLYMO RUOŠIMAS 5-3-2024 9:34:30 AM 18743 2024-05-02 14:08 CAD 5-3-2024 11:11:37 AM 18743 2024-05-02 14:08 SĄMATOS RUOŠIMAS 5-3-2024 1:21:30 PM 18743 2024-05-02 14:08 MARŽOS NUSTATYMAS 5-7-2024 8:26:21 AM 18743 2024-05-02 14:08 PASIŪLYMO PATIKRINIM 5-7-2024 10:04:43 AM 18743 2024-05-02 14:08 PASIŪLYMO RUOŠIMAS 5-7-2024 5:27:10 PM 18743 2024-05-02 14:08 PASIŪLYMAS IŠSIŲSTAS 5-7-2024 7:43:07 PM 18691 2024-05-30 16:15 PAKLAUSIMAS 4-26-2024 11:15:04 AM 18691 2024-05-30 16:15 CAD 4-26-2024 11:25:35 AM 18691 2024-05-30 16:15 SĄMATOS RUOŠIMAS 4-26-2024 12:46:32 PM 18691 2024-05-30 16:15 MARŽOS NUSTATYMAS 4-26-2024 8:14:19 PM 18691 2024-05-30 16:15 PASIŪLYMO RUOŠIMAS 4-26-2024 8:15:53 PM 18691 2024-05-30 16:15 PASIŪLYMAS IŠSIŲSTAS 4-26-2024 8:27:21 PM 18691 2024-05-30 16:15 SĄMATOS RUOŠIMAS 4-29-2024 9:32:12 AM 18691 2024-05-30 16:15 MARŽOS NUSTATYMAS 4-29-2024 1:55:51 PM 18691 2024-05-30 16:15 PASIŪLYMO RUOŠIMAS 4-29-2024 2:07:03 PM 18691 2024-05-30 16:15 PASIŪLYMAS IŠSIŲSTAS 4-29-2024 2:15:28 PM 18691 2024-05-30 16:15 SĄMATOS RUOŠIMAS 5-2-2024 9:05:54 AM 18691 2024-05-30 16:15 MARŽOS NUSTATYMAS 5-2-2024 11:38:19 AM 18691 2024-05-30 16:15 SĄMATOS RUOŠIMAS 5-3-2024 12:09:02 PM 18691 2024-05-30 16:15 MARŽOS NUSTATYMAS 5-3-2024 1:50:27 PM 18691 2024-05-30 16:15 PASIŪLYMO RUOŠIMAS 5-3-2024 2:15:28 PM 18691 2024-05-30 16:15 PASIŪLYMAS IŠSIŲSTAS 5-3-2024 3:26:07 PM

Sorry, I do not know how to add excel file.

Super User

Hi,

In another column, show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

This is very confusing.  In the table that you shared, just show another column with the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

I can add just screenshot because of the error. But the same numbers is in the earlier my post.

Frequent Visitor
 ID Received date and time New Value Date and time. CAD status min. 18743 2024-05-02 14:08 PAKLAUSIMAS 5-2-2024 3:12:14 PM 18743 2024-05-02 14:08 CAD 5-2-2024 5:08:48 PM 985 18743 2024-05-02 14:08 SĄMATOS RUOŠIMAS 5-3-2024 9:34:08 AM 18743 2024-05-02 14:08 PASIŪLYMO RUOŠIMAS 5-3-2024 9:34:30 AM 18743 2024-05-02 14:08 CAD 5-3-2024 11:11:37 AM 129 18743 2024-05-02 14:08 SĄMATOS RUOŠIMAS 5-3-2024 1:21:30 PM 18743 2024-05-02 14:08 MARŽOS NUSTATYMAS 5-7-2024 8:26:21 AM 18743 2024-05-02 14:08 PASIŪLYMO PATIKRINIM 5-7-2024 10:04:43 AM 18743 2024-05-02 14:08 PASIŪLYMO RUOŠIMAS 5-7-2024 5:27:10 PM 18743 2024-05-02 14:08 PASIŪLYMAS IŠSIŲSTAS 5-7-2024 7:43:07 PM

E.g. SĄMATOS RUOŠIMAS (5-3-2024 9:34:08 AM) minus CAD (5-2-2024 5:08:48 PM) is 985 minutes.

Super User

Hi,

This calculated column formula works

``Column = if(Data[New Value]="CAD",24*60*(CALCULATE(MIN(Data[Date and time]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Date and time]>EARLIER(Data[Date and time])&&Data[New Value]="SĄMATOS RUOŠIMAS"))-Data[Date and time]),BLANK())``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thank You !

Frequent Visitor

Super User

Days Diff Measure : =
VAR ranking =
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Stage] = MAX ( 'Table'[Stage] ) ),
CALCULATE ( MAX ( 'Table'[Date] ) ),
,
ASC
)
VAR result =
DATEDIFF (
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Stage] = MAX ( 'Table'[Stage] )
&& RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Stage] = MAX ( 'Table'[Stage] ) ),
CALCULATE ( MAX ( 'Table'[Date] ) ),
,
ASC
) = ranking - 1
)
),
MAX ( 'Table'[Date] ),
DAY
)
RETURN
result

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

pls provide some sample data and expected output.

Proud to be a Super User!

Frequent Visitor

My sample data

So, I need days difference between first date and last date (in lastdate Column) based on bomno column.

Super User

maybe you can try this

measure=calculate(max(lastdate),allexcept(table,bomno))-calculate(min(lastdate),allexcept(table,bomno))

Proud to be a Super User!

New Member

Need DAX(Power BI) for no of days for Each SGNo from the Above Table.

Super User

what's the expected output? it's better to create a new postand provide the detailed info.

Proud to be a Super User!

Frequent Visitor

Thanks a lot. It worked for me...😊

Super User

you are welcome

Proud to be a Super User!

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 - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors