Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous 

    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)

Anonymous's avatar
Anonymous
Not applicable
5 years ago

hi Anonymous - 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"
)
)
 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks,

     

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

     

    Best, 

     

    Daven