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 ) )

     

     

     

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

    Hi parry2k thank you so much and I'm so sorry but since my real data set is based on a SQL database it won't let me reference the columns in the way you did.  I can get the formula to not give me an error if I tweak it as below but the result is not right (e.g. has values much greater than 540, etc.) My columns are stored in 'Fact Inventory'. I had to add the selectedvalue for it to let me select any column in that table (otherwise it only lets me select other measures), even though it may not be correct to do that.

     

    Trimmed Aging =

        SUMX (
            'Fact Inventory',
            VAR __Date1 = COALESCE (selectedvalue('Fact Inventory'[Date 1a]), selectedvalue('Fact Inventory'[Date 1b] ))
            VAR __Date2 =  selectedvalue('Fact Inventory'[Date 2 (date it leaves inventory)])
            VAR __Diff = DATEDIFF ( __Date1, __Date2, DAY )
            RETURN
                IF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) )
        )
     
    I'm hoping it's possible to just tweak this? 🙂 Please let me know thank you!