Forum Discussion

Helper I

3 years ago

Solved

Difference in date between two columns in a measure?

Hello! I have an Excel version of this that I am trying to duplicate in PBI. I have three date columns Date 1a(stock date), Date1b(different stock date), and Date 2(sell date). I want to use Da...

Need Help

parry2k

3 years ago

Donny620 you can add a measure like this:

Trimmed Aging = 
VAR __Date1 = COALESCE ( SELECTEDVALUE ( Table[Date1] ), Table[Date2] )
VAR __Date2 = SELECTEDVALUE ( Table[Date2] )
VAR __Diff = DATEDIFF ( __Date1, __Date2, DAYS )
RETURN
IF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) )

Donny620

Helper I

3 years ago

parry2k Thank you again! I'm having trouble duplicating the formula (Do I add another SelectedValue function and change 'Days' to 'Day'?) To make it super simple here are two files one an Excel which gives the

'answer' using the required logic and a pivot table, and one PBI which looks at the Excel and tries a formula like yours.

https://drive.google.com/file/d/1rRqD-bkrzi7TG2ioG_VpmeUZd_gM35wc/view?usp=drive_link

https://docs.google.com/spreadsheets/d/1O33eBH4uqJns0Fz-Ip7_22XXEfYQ9gMd/edit?usp=drive_link&ouid=105617650305009849389&rtpof=true&sd=true

Thank you!!

Donny620

Helper I

3 years ago

parry2k Any advice for me here? 🙂