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

[erp]

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

[ryan_mayu · Accepted solution]

@erp 

maybe you can try this

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

[Augustinas]

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_Mathur]

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.

[Augustinas]

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.

[Ashish_Mathur]

Hi,

In another column, show the expected result.

[Ashish_Mathur]

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

[erp]

Thanks for your answers...

[Jihwan_Kim]

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 

[ryan_mayu]

@erp 

pls provide some sample data and expected output.

[erp]

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_mayu · Accepted solution]

@erp 

maybe you can try this

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

[YRK]

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

[ryan_mayu]

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

[erp · Accepted solution]

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

[ryan_mayu]

you are welcome