cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Calculating case age

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

 

CreatDateTimeCloseDateTime

Case Age

 

5/29/2020 5:31:36 PM6/12/2020 11:47:10 AMnull
4/22/2020 11:16:47 AM 147

09/16/2020 11:16:47 AM

 0

09/15/2020 11:16:47 AM

 

 1
   
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Calculating case age

Hi @Daven 

and here is my final version:

 

16-09-_2020_23-31-27.png

 

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)

View solution in original post

5 REPLIES 5
Highlighted
Memorable Member
Memorable Member

Re: Calculating case age

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. 

 

Case Age =
IF (
'ZZZ - CaseAge'[CloseDateTime] = BLANK (),
FORMAT (
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) )
- DATE ( YEAR ( 'ZZZ - CaseAge'[CreatDateTime] ), MONTH ( 'ZZZ - CaseAge'[CreatDateTime] ), DAY ( 'ZZZ - CaseAge'[CreatDateTime] ) ),
"0"
)
)
 
Sumanth_23_0-1600281157399.png

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

 

Highlighted
Super User II
Super User II

Re: Calculating case age

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"

16-09-_2020_23-06-22.png

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Highlighted
Helper I
Helper I

Re: Calculating case age

Thanks,

 

Is it possible to create a column from Transform Data --> Custom Column?

 

Best, 

 

Daven 

Highlighted
Super User II
Super User II

Re: Calculating case age

Hi @Daven 

with a measure you can achieve the expected result like this:

 

16-09-_2020_23-22-52.png

 

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)

Highlighted
Super User II
Super User II

Re: Calculating case age

Hi @Daven 

and here is my final version:

 

16-09-_2020_23-31-27.png

 

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)

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors