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 )
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi sharonmathew

 

You have understood it right. This is your quote

 

"

is it like getting the Max of date 

after filtering card and item desc

where current row date is lesser than current row date."

 

 

 

Why are you not able to use VAR?.... Its available in Excel 2016

 

sharonmathew's avatar
sharonmathew
Helper I
8 years ago

thank you, its beautiful.

 

can i use ALL function before the filter, will it change the calculation. Keeping the rest same.

 

ALLEXCEPT ( Range, Range[name], Range[Itsc] ),
FILTER(ALL ( Range[date] ),Range[date] < MAX ( Range[date] )

 

iam using 2013 i saw VAR there but it gave me sytanx error while typing )) . 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    sharonmathew

     

    There is no need for 2 filters

     

    This one is sufficient

     

    FILTER (
    ALLEXCEPT ( Range, Range[CardNumber], Range[Item Desc] ),
    Range[TxnDate] < MAX ( Range[TxnDate] )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    sharonmathew

     

    In a MEASURE, normally you cannot use EARLIER...Try this

     

    FILTER (
    ALLEXCEPT ( Range, Range[CardNumber], Range[Item Desc] ),
    Range[TxnDate] < MAX ( Range[TxnDate] )
        && Range[newfield] = SELECTEDVALUE ( Range[newfield] )
)
  • 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] )
)

     

  • sharonmathew's avatar
    sharonmathew
    Helper I
    8 years ago

    thanks Again!

  • sharonmathew's avatar
    sharonmathew
    Helper I
    8 years ago
    FILTER (
    ALLEXCEPT ( Range, Range[name], Range[field] ),
    Range[Date] < MAX ( Range[Date] )
)

    hi, could you add one more condition here for me.

     

     for the below code i want >>>  DO this -  only if Range[newfield] = Earlier( Range[newfield]

    Range[Date] < MAX ( Range[Date]
     

     

  • 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.

  • sharonmathew's avatar
    sharonmathew
    Helper I
    8 years ago

    thanks i'll try :))

  • sharonmathew's avatar
    sharonmathew
    Helper I
    8 years ago

    my initial request, you did give me ans. it is working just fine.

     

    and this one i belive should go as a separate thread. so, ill branch here. and be back soon.