Forum Discussion

Donny620's avatar
Donny620
Icon for Helper I rankHelper 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's avatar
    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 ) )

     

     

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
3 years ago

Donny620 check my formula, it is not DATE1, DATE2, there is a parenthesis in between.

  • Donny620's avatar
    Donny620
    Icon for Helper I rankHelper 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!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      You could always just use the Power Query through the 'Transform Data' Tab, if you had some thing like this:

      The  you could use a custom column to achieve what you want:

      if [Date] = null and [Date2] <> null then [Date2] else if [Date] <> null and [Date2] = null then [Date] else if [Date] = null and [Date2] = null then null else List.Max({[Date],[Date2]}) - #duration(0,0,0,(Duration.TotalSeconds([Date]-[Date2]))/2)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

    • Donny620's avatar
      Donny620
      Icon for Helper I rankHelper I
      3 years ago

      parry2k Any advice for me here? 🙂