datediff

[sharonmathew]

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

product 4      1/18/17

what i try to get through DAX is an expression which can bring the results in a third column with the difference between two dates.

row label      date            Result

product 1      1/15/17  

product 2      1/16/17       1

product 4      1/18/17        2

kindly help me build it and thanks in adv

[Interkoubess]

Hi @sharonmathew,

You can create a calculated column with this formula where my tble is called Table1:

IF(ISBLANK(CALCULATE(MAX(Table1[date]),ALL(Table1),Table1[date]<EARLIER(Table1[date]))),BLANK(),Table1[date]-CALCULATE(MAX(Table1[date]),ALL(Table1),Table1[date]<EARLIER(Table1[date])))

Hope it helps...

Ninter

[sharonmathew]

hi Thank you for this great effort, however, i was seeking a measure, preferably.

I would like to detail my query.

Name	Produt	Date	my result is here: Difference in days
John	Product 3	29-Apr-17	blank()
		02-Aug-17	95
		13-Nov-17	103
	Product 3 Total		blank()
John Total			blank()
Mark	Product 3	13-Jun-17	blank()
		15-Oct-17	124
	Product 3 Total		blank()
Mark Total			blank()
Peter	Product 3	21-May-17	blank()
		21-Nov-17	184
	Product 3 Total		blank()
Peter Total			blank()

so, the pivot table contains slicers and top 10 filters.

i guess it should respect the slicers and filters on the table.

then, here we are looking for some members has bought the products on certain dates. 

after filtering the product. in this case it is product 3. the measure should calculate from the visible column in the pivot table , the difference of dates from its previous row.

please help me with this if its fine with you.

[Ashish_Mathur]

Hi,

Share the link from where i can download your workbook.

[sharonmathew]

Hi,

i have posted it here aslo. kindly visit this link.

http://powerpivotforum.com.au/viewtopic.php?f=6&t=1054&p=3310#p3310

Thanks

Sharon

[Zubair_Muhammad · Accepted solution]

@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]

Hi thanks aton for this help.

i' am going to check and will come back soon, i hope it should work for me

[sharonmathew]

Hi There,

thank you very much for this beautiful present. 

I want to discuss a bit more on the var part.

i got this working like this.

1st Measure:<<<<<<<<<<<<<<<<<<<<<<

Prev Date:=CALCULATE (
MAX ( Range[Date] ),
FILTER (
ALLEXCEPT ( Range, Range[cardnumber], Range[field] ),
Range[Date] < MAX ( Range[Date] )
)
)

2nd Measure: <<<<<<<<<<<<<<<<<<<

Present date:=IF(HASONEVALUE(Range[Date]),
IF(
NOT(
ISBLANK([Prev Date])
),
MAX(Range[Date])-[Prev Date],
BLANK()))

how would you explain this part. 

MAX ( Range[Date] ),
FILTER (ALLEXCEPT ( Range, Range[cardnumber], Range[field] ),
Range[Date] < MAX ( Range[Date] ))

is it like get the Max of date 

after filtering card and field

where current row date is lesser than current row date.

did i get it right .

Also, i couldn't work in the same way as VAR is. so how can i actually make the same code you wrote.

and one more mention, is there a require ment for       ALL(Range[Date])      inside the FILTER.

however, it is am very happy as it is operational for me and has saved me from loads of troubles.

[Zubair_Muhammad]

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]

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]

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

[sharonmathew]

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]

[Zubair_Muhammad]

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

[sharonmathew]

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

[Zubair_Muhammad]

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]

thanks i'll try :))

[sharonmathew]

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.

[sharonmathew]

thanks Again!

[sharonmathew]

Dear Com,

I am slightly becoming patient to finding this. kindly help me if you can. and thank you.

i have a pivot table. it has a list of products and dates.

say

product   dates

pro1        1/1/17

pro2        2/2/17

pro3       3/3/17

pro4       4/417

i want to calculate a measure to return the date difference in days [ after the slicer is applied]

result should look like,

product   dates      Result

pro1        1/1/17     

pro3       3/3/17    [value = 3/3/17 - 1/1/17] as the pro2 has respected the slicer

pro4       4/417      [value = 4/417 - 3/3/17]