Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have below columns and would like to create a new column Case Age that willl show days the case is open for.
If today is Sep 16 2020 then the Case Age column should show below. If there is closeddatetime entered then null for Case Age.
Thanks
Daven
CreatDateTime | CloseDateTime | Case Age
|
5/29/2020 5:31:36 PM | 6/12/2020 11:47:10 AM | null |
4/22/2020 11:16:47 AM | 147 | |
09/16/2020 11:16:47 AM | 0 | |
09/15/2020 11:16:47 AM
| 1 | |
Solved! Go to Solution.
Hi @Daven
and here is my final version:
Measure =
VAR _CurrentCreatDateTime = CONVERT(MAX('Table'[CreatDateTime]),DOUBLE)
VAR _CurrentCloseDateTime = MAX('Table'[CloseDateTime])
RETURN
IF(
_CurrentCloseDateTime = BLANK(),
INT(CONVERT(NOW(),DOUBLE) - _CurrentCreatDateTime) ,
BLANK()
)
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi @Daven
and here is my final version:
Measure =
VAR _CurrentCreatDateTime = CONVERT(MAX('Table'[CreatDateTime]),DOUBLE)
VAR _CurrentCloseDateTime = MAX('Table'[CloseDateTime])
RETURN
IF(
_CurrentCloseDateTime = BLANK(),
INT(CONVERT(NOW(),DOUBLE) - _CurrentCreatDateTime) ,
BLANK()
)
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi @Daven
with a measure you can achieve the expected result like this:
Measure =
VAR _CurrentCreatDateTime = CONVERT ( MAX ( 'Table'[CreatDateTime] ), DOUBLE )
VAR _CurrentCloseDateTime = MAX ( 'Table'[CloseDateTime] )
RETURN
IF (
_CurrentCloseDateTime = BLANK (),
INT (
CALCULATE (
CONVERT ( NOW (), DOUBLE ) - _CurrentCreatDateTime,
'Table'[CloseDateTime] = BLANK ()
)
),
BLANK ()
)
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi @Daven
you can try it very easy with Power Query like this (don't bother with date and time format in figur below, it's localized):
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcm7DcAgDAXAVZBrJPsZ7Ah3GQCJHrH/GvkVaaK0d3OSsTZWUUkWBVE8jU6ZnKEPA1G3gKS908qTKus78DOvyXSnNIb/rn3vOgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CreatDateTime = _t, CloseDateTime = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"CreatDateTime", type datetime}}, "en-US"),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"CloseDateTime", type datetime}}, "en-US"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale1", "Case Age", each if [CloseDateTime] = null then DateTime.LocalNow() - [CreatDateTime] else null),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Case Age", type number}}),
#"Rounded Off" = Table.TransformColumns(#"Changed Type",{{"Case Age", each Number.Round(_, 0), type number}})
in
#"Rounded Off"
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
hi @Daven - you can achieve this by using a calculated column as seen in the below screenshot
Essentially I am checking if CloseDateTime is Blank and when it is I am building TODAY's date from Year, Month & Day (without time) and subtracting the CreateDateTime (again using Year, Month, Day) and formatting the difference as a number.
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
Proud to be a Super User!
Thanks,
Is it possible to create a column from Transform Data --> Custom Column?
Best,
Daven
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |