Forum Discussion

socal225's avatar
socal225
Frequent Visitor
6 years ago

Date diff with duplicate values

Hello all,

 

I am trying to create a calculated column that gives the age (in days) between a date in a column and the current date. I then want to create an additional measure based on this one. The date column has duplicate values in a second column called "record no," and the problem is that when I create the "age" measure it sums up the age on the duplicate "record no" column, and that affects the second measure based on other parameters. In a nutshell, I want to see the age for each "record no" based on the date, but I don't want it to aggregate based on the multiple duplicate rows of record no. Thank you in advance!

 

Need Help

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    You could do it as a calcualted table that you link back to your main table based on the item number.

     

    Table 2 =
ADDCOLUMNS (
    SUMMARIZE ( 'Table', 'Table'[Item], 'Table'[Date] ),
    "DateDiff", DATEDIFF ( 'Table'[Date], TODAY (), DAY )
)

    If you can do it as a measure instead that might be easier.

    DateDiff = 
VAR _ItemDate = SELECTEDVALUE('Table'[Date])
RETURN DATEDIFF(_ItemDate,TODAY(),DAY)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi socal225 ,

     

    What are the other measures?

     

    You can Calculated Columns

     

    Days Elapsed = DATEDIFF('Table'[DateTime],Today(),DAY)
     
    Ageing = IF (Table[Days Elapsed] > 50, "above 50 Days", "Below 50 Days")
     
     
    and then create a measure
    Ageing Details = COUNT(Table[RecordNo])
     
     
     
     
     Regards,
    Harsh Nathani
     
  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi socal225 , 

    If possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.