How to calculate difference in dates between last purchases

[Anonymous]

I want to calculate the differences between two dates. One is the latest purchase date and the other one is the latest purchase date that is before the latest purchased date.

Customer	Purchased date	Manufacturer	Product
123123	12/2/2018	Apple	T23
123123	10/15/2018	Banana	K35
123123	5/30/2018	Banana	T23
123123	3/4/2018	Apple	T27
123123	2/16/2017	Apple	T56
123123	6/22/2017	Banana	T56

 For this situation, I want to get Apple company's purchases dates and differences between 3/4/2018 amd 2/16/2017. 12/2 and 2/16 do not need because 3/4/2018 can give me smaller day count.

Also, if customer bought Banana's product for the first time then it should return 0 daycount.

These are my measure formulas.

The Lastest date= CALCULATE(MIN('DATA'[Purchased Date]), FILTER('DATA','DATA'[Manufacturer]="Apple"))

Last Laste purchased =

CALCULATE(MIN('DATA'[Purchased Date]),'DATA'[Manufacturer]="Apple", FILTER('UCC DATA','DATA'[Purchased Date] <MIN('DATA'[Purchased Date])))

days since last purchase = IF(ISBLANK([The Lastest date]),BLANK(),VALUE([Last Purchased date]-[The Lastest date]))

The result does not giving me what I wanted.

Can anyone help me on this?

[v-rzhou-msft]

Hi @Anonymous 

Here I build a table like yours to have a test.

Add an index column sorted by Purchased Date for each Manufacturer and each Customer.

Index =
RANKX (
    FILTER (
        'Table',
        'Table'[Manufacturer] = EARLIER ( 'Table'[Manufacturer] )
            && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
    ),
    'Table'[Purchased date],
    ,
    DESC
)

Result:

Then build a measure to achieve your goal.

DayDiff = 
VAR _TheLatestDate =
    MAXX (
        FILTER (
            ALL ( DATA ),
            DATA[Customer] = MAX ( DATA[Customer] )
                && DATA[Manufacturer] = MAX ( DATA[Manufacturer] )
        ),
        DATA[Purchased date]
    )
VAR _TheLatestDatebefore =
    CALCULATE (
        MAX ( DATA[Purchased date] ),
        FILTER (
            ALL ( DATA ),
            DATA[Customer] = MAX ( DATA[Customer] )
                && DATA[Manufacturer] = MAX ( DATA[Manufacturer] )
                && DATA[Index] > 1
        )
    )
VAR _MAXINDEX =
    MAXX (
        FILTER (
            ALL ( DATA ),
            DATA[Customer] = MAX ( DATA[Customer] )
                && DATA[Manufacturer] = MAX ( DATA[Manufacturer] )
        ),
        DATA[Index]
    )
RETURN
    IF ( _MAXINDEX = 1, 0, DATEDIFF ( _TheLatestDatebefore, _TheLatestDate, DAY ) )

Result:

If this reply still couldn't help you solve this problem, please provide me more details.

From your statement: DateA =latest purchase date, DateB=  the latest purchase date before DateA.

But in your example, when we calculate the day count in Apple, DateA = 2018/12/2, DateB= 2018/3/4.

Why here you calculate the day count by DateA=2018/3/4,DateB = 2018/2/16? If my understanding is wrong, please tell me more about your calculate logic. Or you can share your pbix file with me by your Onedrive for Business.

You can download the pbix file from this link: How to calculate difference in dates between last purchases

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

@RicoZhou For the customer 109527 apple 2019/12/31 in the last picture, why is the day diff is not the difference between the 2020/6/3 and 2019/12/31? also 123123 apple2016/12/2 has 381 day diff. Is there a way that I can change it with differences between eac of the larger date in different year?

ex) 2017/03/04 and 2017/07/06 and 2014/03/04 are the purchased dates. I want the days diff on 2014/03/04 and 2017/03/04 and 0 for the 2017/07/06.

[v-rzhou-msft · Accepted solution]

Hello @dndrms2105

New index column:

Index = 
RANKX (
    FILTER (
        'DATA',
        'DATA'[Manufacturer] = EARLIER ( 'DATA'[Manufacturer] )
            && 'DATA'[Customer] = EARLIER ( 'DATA'[Customer] )
    ),
    'DATA'[Purchased date],
    ,
    ASC
)

New measurement:

DayDiff = 
VAR _A =
    MAX ( DATA[Purchased date] )
VAR _B =
    CALCULATE (
        MAX ( DATA[Purchased date] ),
        FILTER (
            ALLEXCEPT ( DATA, DATA[Manufacturer], DATA[Customer] ),
            DATA[Index]
                = SUM ( DATA[Index] ) - 1
        )
    )
VAR _DATEDIFF =
    DATEDIFF ( _B, _A, DAY )
RETURN
    _DATEDIFF

MAXDayDiff = 
VAR _MAXDATEDIFF =
    MAXX ( ALLEXCEPT ( DATA, DATA[Customer], DATA[Manufacturer] ), [DayDiff] )
VAR _IndexforMaxDatediff =
    CALCULATE (
        SUM ( DATA[Index] ),
        FILTER (
            ALLEXCEPT ( DATA, DATA[Customer], DATA[Manufacturer] ),
            [DayDiff] = _MAXDATEDIFF
        )
    )
VAR _MAXINDEX =
    MAXX (
        FILTER (
            ALL ( DATA ),
            DATA[Customer] = MAX ( DATA[Customer] )
                && DATA[Manufacturer] = MAX ( DATA[Manufacturer] )
        ),
        DATA[Index]
    )
RETURN
    IF (
        _MAXINDEX = 1,
        0,
        IF (
            SUM ( DATA[Index] ) = _IndexforMaxDatediff
                || SUM ( DATA[Index] ) = _IndexforMaxDatediff - 1,
            _MAXDATEDIFF,
            0
        )
    )

Result:

By maxDaydiff measured, you can get the result of the largest day in daydiffs.

ex:Customer 109527 Apple Manufacturer: 2014/3/4 and 2017/3/4(daydiff 1096), 2017/3/4 and 2017/7/6(daydiff 124),

so we showed 1096 in 2014/3/4 and 2017/3/4, and showed 0 in 2017/7/6.

You can download the pbix file from this link: How to calculate the date difference between the latest purchases

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it more quickly.

[Anonymous]

@RicoZhou Why 123123 on 2016/ 12/2 shows 0? and Is there a way to filter one specific manufacturer? like only for manufacturer Apple.

[v-rzhou-msft]

Hi @Anonymous 

I think you need the larger daydiff between 123123 apple.

ex between 2016/12/2 and 2017/2/16 daydiff = 76, between 2017/2/16 and 2018/3/4 daydiff = 381.

So the result is 381 and show up behind 2017/2/16 and 2018/3/4, and 2016/12/2 show 0.

Just like the example you given to me.

If there is anything wrong in my understanding, please tell me what result you want from these three data.( 2016/12/2, 2017/2/16,2018/3/4)

If you want to filter one specific manufacturer, you may try filters or slicer.

Filters:

Slicer:

You can download the pbix file from this link: How to calculate the date difference between the last purchases

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Greg_Deckler]

@Anonymous - So basically you want to do a calculation between two rows. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586