Forum Discussion
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..
Anonymous
maybe you can try this
measure=calculate(max(lastdate),allexcept(table,bomno))-calculate(min(lastdate),allexcept(table,bomno))
- Anonymous5 years ago
Thanks a lot. It worked for me...😊
18 Replies
- ryan_mayuSuper User
Anonymous
pls provide some sample data and expected output.
- AnonymousNot applicable
My sample data
So, I need days difference between first date and last date (in lastdate Column) based on bomno column.
Thanks in advance..
- ryan_mayuSuper User
Anonymous
maybe you can try this
measure=calculate(max(lastdate),allexcept(table,bomno))-calculate(min(lastdate),allexcept(table,bomno))
- Jihwan_KimSuper 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
resulthttps://www.dropbox.com/s/rwbkhoz3ir6d789/erp.pbix?dl=0
- AnonymousNot applicable
Thanks for your answers...
- AugustinasFrequent 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.
- Ashish_MathurSuper 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.
- AugustinasFrequent 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.