Skip to main content
cancel
Showing results for 
Search instead 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

Reply
erp
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?

Thanks in Advance..

2 ACCEPTED SOLUTIONS

@erp 

maybe you can try this

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

erp
Frequent Visitor

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

View solution in original post

18 REPLIES 18
Augustinas
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:

Augustinas_1-1715848844185.png

Can anyone help? thanks in advance.

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
https://www.linkedin.com/in/excelenthusiasts/

IDReceived date and timeNew ValueDate and time.
187942024-05-06 14:19PAKLAUSIMAS5-7-2024 9:01:59 AM
187942024-05-06 14:19CAD5-9-2024 11:37:04 AM
187942024-05-06 14:19SĄMATOS RUOŠIMAS5-9-2024 11:56:39 AM
187942024-05-06 14:19CAD5-9-2024 11:58:05 AM
187942024-05-06 14:19SĄMATOS RUOŠIMAS5-9-2024 1:01:58 PM
187942024-05-06 14:19MARŽOS NUSTATYMAS5-16-2024 4:56:39 PM
187942024-05-06 14:19PASIŪLYMO RUOŠIMAS5-16-2024 5:06:06 PM
187942024-05-06 14:19PASIŪLYMAS IŠSIŲSTAS5-20-2024 11:35:32 AM
187432024-05-02 14:08PAKLAUSIMAS5-2-2024 3:12:14 PM
187432024-05-02 14:08CAD5-2-2024 5:08:48 PM
187432024-05-02 14:08SĄMATOS RUOŠIMAS5-3-2024 9:34:08 AM
187432024-05-02 14:08PASIŪLYMO RUOŠIMAS5-3-2024 9:34:30 AM
187432024-05-02 14:08CAD5-3-2024 11:11:37 AM
187432024-05-02 14:08SĄMATOS RUOŠIMAS5-3-2024 1:21:30 PM
187432024-05-02 14:08MARŽOS NUSTATYMAS5-7-2024 8:26:21 AM
187432024-05-02 14:08PASIŪLYMO PATIKRINIM5-7-2024 10:04:43 AM
187432024-05-02 14:08PASIŪLYMO RUOŠIMAS5-7-2024 5:27:10 PM
187432024-05-02 14:08PASIŪLYMAS IŠSIŲSTAS5-7-2024 7:43:07 PM
186912024-05-30 16:15PAKLAUSIMAS4-26-2024 11:15:04 AM
186912024-05-30 16:15CAD4-26-2024 11:25:35 AM
186912024-05-30 16:15SĄMATOS RUOŠIMAS4-26-2024 12:46:32 PM
186912024-05-30 16:15MARŽOS NUSTATYMAS4-26-2024 8:14:19 PM
186912024-05-30 16:15PASIŪLYMO RUOŠIMAS4-26-2024 8:15:53 PM
186912024-05-30 16:15PASIŪLYMAS IŠSIŲSTAS4-26-2024 8:27:21 PM
186912024-05-30 16:15SĄMATOS RUOŠIMAS4-29-2024 9:32:12 AM
186912024-05-30 16:15MARŽOS NUSTATYMAS4-29-2024 1:55:51 PM
186912024-05-30 16:15PASIŪLYMO RUOŠIMAS4-29-2024 2:07:03 PM
186912024-05-30 16:15PASIŪLYMAS IŠSIŲSTAS4-29-2024 2:15:28 PM
186912024-05-30 16:15SĄMATOS RUOŠIMAS5-2-2024 9:05:54 AM
186912024-05-30 16:15MARŽOS NUSTATYMAS5-2-2024 11:38:19 AM
186912024-05-30 16:15SĄMATOS RUOŠIMAS5-3-2024 12:09:02 PM
186912024-05-30 16:15MARŽOS NUSTATYMAS5-3-2024 1:50:27 PM
186912024-05-30 16:15PASIŪLYMO RUOŠIMAS5-3-2024 2:15:28 PM
186912024-05-30 16:15PASIŪLYMAS IŠSIŲSTAS5-3-2024 3:26:07 PM

 

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

Hi,

In another column, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
https://www.linkedin.com/in/excelenthusiasts/

Augustinas_0-1717420146722.png

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

IDReceived date and timeNew ValueDate and time.CAD status min.
187432024-05-02 14:08PAKLAUSIMAS5-2-2024 3:12:14 PM 
187432024-05-02 14:08CAD5-2-2024 5:08:48 PM985
187432024-05-02 14:08SĄMATOS RUOŠIMAS5-3-2024 9:34:08 AM 
187432024-05-02 14:08PASIŪLYMO RUOŠIMAS5-3-2024 9:34:30 AM 
187432024-05-02 14:08CAD5-3-2024 11:11:37 AM129
187432024-05-02 14:08SĄMATOS RUOŠIMAS5-3-2024 1:21:30 PM 
187432024-05-02 14:08MARŽOS NUSTATYMAS5-7-2024 8:26:21 AM 
187432024-05-02 14:08PASIŪLYMO PATIKRINIM5-7-2024 10:04:43 AM 
187432024-05-02 14:08PASIŪLYMO RUOŠIMAS5-7-2024 5:27:10 PM 
187432024-05-02 14:08PASIŪLYMAS IŠSIŲSTAS5-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.

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.

Ashish_Mathur_0-1717469653990.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank You !

erp
Frequent Visitor

Thanks for your answers...

Jihwan_Kim
Super User
Super User

 

Picture2.png

 

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

 

 

https://www.dropbox.com/s/rwbkhoz3ir6d789/erp.pbix?dl=0 

 

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.


Go to My LinkedIn Page


ryan_mayu
Super User
Super User

@erp 

pls provide some sample data and expected output.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




erp
Frequent Visitor

My sample data

erp_0-1624607974459.png

erp_1-1624608058008.png

 

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

Thanks in advance..

 

@erp 

maybe you can try this

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1.png

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




erp
Frequent Visitor

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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

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

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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