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

Can you share what you expression are you using based on what I gave you. You are trying me to guess things here and making things over complicated.

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

    So sorry!  See below, where 'Fact Inventory' is the name of the SQL database table.  I only added selectedvalue function because otherwise it won't let me insert/find columns like 'Date 1a' (it only 'finds' calculations/measures).  Does this help?

     

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