Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have pbix file in the below one Drive location
For some reason the column Sales_DateDiff is not showing correctly for the below highlighted rows
I am expecting them to be the below time differences
Example when running on T-SQL which returns the results I am after
Declare @StartDate as DateTime
Declare @EndDate as DateTime
Set @StartDate = '2020-12-10 15:20:00.000'
Set @EndDate = '2020-12-10 17:20:00.000'
SELECT
convert(varchar(2),FORMAT(DATEDIFF(s, @StartDate, @EndDate)/3600,'0#'))+':'+convert(varchar(2),FORMAT(DATEDIFF(s, @StartDate, @EndDate)%3600/60,'0#'))+':'+convert(varchar(2),FORMAT(DATEDIFF(s, @StartDate, @EndDate)%60,'0#')) AS [hh:mm:ss 2]
Solved! Go to Solution.
Hi @AvPowerBI
Download your PBIX file with modification.
FWIW I'd do all of these time calc in Power Query, but to solve thisnow you can create a column and subtract EndDateTime from StartDateTime
NOTE: in row 5, 6 and 7 the difference between times is exactly as shown in my column. Your column is out by 1 second.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi @AvPowerBI
I noticed that in your Sales_DateDiff column, you have codes dealing with DateDiff greater than one day, so above solutions are probably not suitable because they forgot to deal with this. You could modify your column codes like below to deal with it.
Sales_DateDiff 2 =
VAR _Duration = 'Sales'[EndDateTime] - 'Sales'[StartDateTime]
VAR _Day = DATEDIFF('Sales'[StartDateTime], 'Sales'[EndDateTime], DAY)
VAR _Hrs = HOUR(_Duration)
VAR _Min = MINUTE(_Duration)
VAR _Sec = SECOND(_Duration)
VAR _DayStr = IF(_Day = 0, "", _Day & "d ")
VAR _HrsStr = RIGHT("0" & _Hrs, 2)
VAR _MinStr = RIGHT("0" & _Min, 2)
VAR _SecStr = RIGHT("0" & _Sec, 2)
RETURN IF(
'Sales'[EndDateKey] = -1,
BLANK(),
_DayStr & _HrsStr & ":" & _MinStr & ":" & _SecStr
)
Additionally, you could also add custom columns in Power Query Editor. Suppose already have StartDateTime and EndDateTime columns in model.
Duration = [End] - [Start]
DateDiff
= (if Duration.Days([Duration])=0 then "" else Number.ToText(Duration.Days([Duration])) &"d ") & Text.End(Duration.ToText([Duration]),8)
All codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9Q3MjAyUDCxMjAAIiUdkJgxRMwUKharg6zSDFklVMwSpjIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [End] - [Start]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "DateDiff", each (if Duration.Days([Duration])=0 then "" else Number.ToText(Duration.Days([Duration])) &"d ") & Text.End(Duration.ToText([Duration]),8))
in
#"Added Custom1"
Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Hi @AvPowerBI
I noticed that in your Sales_DateDiff column, you have codes dealing with DateDiff greater than one day, so above solutions are probably not suitable because they forgot to deal with this. You could modify your column codes like below to deal with it.
Sales_DateDiff 2 =
VAR _Duration = 'Sales'[EndDateTime] - 'Sales'[StartDateTime]
VAR _Day = DATEDIFF('Sales'[StartDateTime], 'Sales'[EndDateTime], DAY)
VAR _Hrs = HOUR(_Duration)
VAR _Min = MINUTE(_Duration)
VAR _Sec = SECOND(_Duration)
VAR _DayStr = IF(_Day = 0, "", _Day & "d ")
VAR _HrsStr = RIGHT("0" & _Hrs, 2)
VAR _MinStr = RIGHT("0" & _Min, 2)
VAR _SecStr = RIGHT("0" & _Sec, 2)
RETURN IF(
'Sales'[EndDateKey] = -1,
BLANK(),
_DayStr & _HrsStr & ":" & _MinStr & ":" & _SecStr
)
Additionally, you could also add custom columns in Power Query Editor. Suppose already have StartDateTime and EndDateTime columns in model.
Duration = [End] - [Start]
DateDiff
= (if Duration.Days([Duration])=0 then "" else Number.ToText(Duration.Days([Duration])) &"d ") & Text.End(Duration.ToText([Duration]),8)
All codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9Q3MjAyUDCxMjAAIiUdkJgxRMwUKharg6zSDFklVMwSpjIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [End] - [Start]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "DateDiff", each (if Duration.Days([Duration])=0 then "" else Number.ToText(Duration.Days([Duration])) &"d ") & Text.End(Duration.ToText([Duration]),8))
in
#"Added Custom1"
Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
@AvPowerBI , Just subtraction in power query or DAx should give you diff in duration .
Try a new column like
new column = [endDatetime] - [startdatetime]
@AvPowerBI You don't need the LOOKUPVALUE to other tables to do this, you can do all of it in Power Query since you're adding columns (Not measures).
Please try pasting this code into advanced editor of a new blank query and see if you are happy with the results:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZC5EcAgDAR7ISaQzoChFob+27CQHJjPRDfLLDpRqyMQBecdJDAoS+REciaWlTW/N26sRuE/I6+GhINRunGNrZTFQysmvR1mGLOmG4Pf9zhOLIUkbGPA/iV8Z8D2QJ/RHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesOrder = _t, StartDateKey = _t, StartDateTimeKey = _t, EndDateKey = _t, EndDateTimeKey = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesOrder", Int64.Type}, {"StartDateKey", type text}, {"StartDateTimeKey", type text}, {"EndDateKey", type text}, {"EndDateTimeKey", type text}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Start Date", each Date.From(Text.From([StartDateKey], "en-US")), type date),
#"Inserted Date1" = Table.AddColumn(#"Inserted Date", "End Date", each Date.From(Text.From([EndDateKey], "en-US")), type date),
#"Added Start Time" = Table.AddColumn(#"Inserted Date1", "Start Time", each Time.FromText([StartDateTimeKey])),
#"Added Custom" = Table.AddColumn(#"Added Start Time", "End Time", each Time.FromText([EndDateTimeKey])),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Start Date", type text}, {"Start Time", type text}}, "en-US"),{"Start Date", "Start Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Start Date Time"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Start Date Time", type datetime}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"End Date", type text}, {"End Time", type text}}, "en-US"),{"End Date", "End Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"End Date Time"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns1",{{"End Date Time", type datetime}}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type2", "Subtraction", each [End Date Time] - [Start Date Time], type duration),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Date Difference"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date Difference", type text}})
in
#"Changed Type3"
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AvPowerBI
Download your PBIX file with modification.
FWIW I'd do all of these time calc in Power Query, but to solve thisnow you can create a column and subtract EndDateTime from StartDateTime
NOTE: in row 5, 6 and 7 the difference between times is exactly as shown in my column. Your column is out by 1 second.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
177 | |
85 | |
70 | |
63 | |
55 |