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 the measure I gave you is this one, don't know which one you are using:

 

Trimmed Aging = 

    SUMX ( 
        'Data', 
        VAR __Date1 = COALESCE ( Data[Date 1a], Data[Date 1b] )
        VAR __Date2 = Data[Date 2 (date it leaves inventory)] 
        VAR __Diff = DATEDIFF ( __Date1, __Date2, DAY )
        RETURN 
            IF ( __Diff < 5, 5, IF ( __Diff > 540, 540, __Diff ) )
    )
  • Donny620's avatar
    Donny620
    Icon for Helper I rankHelper I
    3 years ago

    parry2k Agreed, what I mean is that the file I sent was based on sample data, but on my real data I am unable to use this measure as is because I can't reference columns in that measure.  If I try to type in Data[Date 1a], it won't come up, it will only show me other measures/calculations.  The only way it would let me reference it was with a function like selectedvalue.  Does that make sense? 😞 Thanks!