cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!

 

Capture.JPG

3 REPLIES 3
Community Support
Community Support

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.

Super User III
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])
 
1.jpg
 
 
 
 Regards,
Harsh Nathani
 
Super User II
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 =
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)

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

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