cancel
Showing results for
Did you mean:
New Member

## 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!

3 REPLIES 3
Community Support

Hi @socal225 ,

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.

Super User III

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

Super User II

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

``````Table 2 =
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)``````

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!