Forum Discussion

sharonmathew's avatar
sharonmathew
Helper I
8 years ago
Solved

datediff

Hi All, i have been struggling to get this sorted.   first of all thank you. ok, i have a pivot table as shown below. row label      date product 1      1/15/17 product 2      1/16/17 produ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    sharonmathew

     

    Try this MEASURE

     

    MEASURE =
VAR PreviousDate =
    CALCULATE (
        MAX ( Range[TxnDate] ),
        FILTER (
            ALLEXCEPT ( Range, Range[Name], Range[product] ),
            Range[TxnDate] < MAX ( Range[TxnDate] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Range[TxnDate] ),
        IF (
            NOT ( ISBLANK ( PreviousDate ) ),
            DATEDIFF ( PreviousDate, MAX ( Range[TxnDate] ), DAY )
        )
    )
sharonmathew's avatar
sharonmathew
Helper I
8 years ago

i really appreciate this, but my excel doesn't have selected value it 2013. any hpe.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi sharonmathew

 

Try this

 

FILTER (
    ALLEXCEPT ( Range, Range[CardNumber], Range[Item Desc] ),
    Range[TxnDate] < MAX ( Range[TxnDate] )
        && Range[newfield] = FirstNonBlank ( Range[newfield],1 )
)

 

or this one

 

FILTER (
    ALLEXCEPT ( Range, Range[CardNumber], Range[Item Desc] ),
    Range[TxnDate] < MAX ( Range[TxnDate] )
        && Range[newfield] = VALUES ( Range[newfield] )
)