Calculate last buy an prior buy

[JdBernate]

Hello

Im trying to find out  changes on costs of some items through time , so i have to calculate the last buy and the prior buy by item. So far i calculated the last buy using the next script:

Lastbuy=

CALCULATE(sum(buy[buy value]);FILTER('buy';MONTH(MAX('buy[Date]))))

Like you can see i need to compare the buy by month.

Im having troubles to calculate the prior buy to "LastBuy". I haved try this:

PriorBuy =
VAR LastDate=
    month(MAX('Buy'[date]))
    
RETURN
CALCULATE(sum('buy'[Buy Value]);FILTER('TBuy';LastDate>month('Buy'[Fecha Ingreso])))

But is not working!!. My objetive is to obtain the prior buy doesnt matter if it was the exactly prior month or 4 months ago, for instance:

Product:

X

Last buy

$$$$ (for instance the buys of May 2018)

PriorBuy

$$$$ (Should be the buy of january 2018, the prior buy to product X).

My model is this table Buy, a calendar table and another tables that doesnt have relationship with this issue

I hope you guys can help me

My best Regards

[v-shex-msft · Accepted solution]

HI @JdBernate,

I'd like some sample data and expected result to clarify your requirement.

In addition, you can try to use below formula if you want to calculate product sales in previous four month.

PriorBuy =
VAR LastDate =
    MAX ( 'Buy'[date] )
RETURN
    CALCULATE (
        SUM ( 'Buy'[Buy Value] );
        FILTER (
            'TBuy';
            FORMAT ( 'Buy'[Fecha Ingreso]; "mm/yyyy" )
                = FORMAT (
                    DATE ( YEAR ( lastdate ); MONTH ( lastdate ) - 4; DAY ( lastdate ) );
                    "mm/yyyy"
                )
        );
        VALUES ( 'Buy'[Product] )
    )

Regards,

Xiaoxin Sheng

[JdBernate]

Thank you very much my friend!!.  Your solution was very useful

My best regards

Juan Diego.