Calculate variance from previous date

[cong_nguyen_acc]

Hi,

I have a problem to solve like this;

There's a data table contains sales revenue of items by each date. The goal is to create a column that calculates the variance value between the date with the previous date. For examples: 

For the item A on 14/06/2019 , variance should be  ; 35,040 - 16,320 = 18,720.

For the item B on 26/06/2019 , variance should be  ; 85,260 - 190,680 = -105,420.

SKU_ID	Invoice_Date	Amount
A	16/05/2019	16,320
A	14/06/2019	35,040
A	15/06/2019	17,520
A	03/12/2019	29,760
A	07/02/2020	32,640
A	31/03/2020	32,640
A	08/04/2020	32,640
A	10/04/2020	32,640
B	15/05/2019	95,340
B	16/05/2019	572,040
B	17/05/2019	190,680
B	26/06/2019	85,260
B	28/06/2019	255,780
B	22/07/2019	85,260
B	23/10/2019	27,300,000

Thanks all,

[v-xicai]

Hi @cong_nguyen_acc ,

You may create measure like DAX below.

Measure =
var PrevDate = CALCULATE(MAX(Table[Invoice_Date]), FILTER(ALLSELECTED(Table), Table[SKU_ID]=MAX(Table[SKU_ID])&&Table[Invoice_Date]<MAX(Table[Invoice_Date])))
return
MAX(Table[Amount]) -CALCULATE(MAX(Table[Amount]), FILTER(ALLSELECTED(Table), Table[SKU_ID]=MAX(Table[SKU_ID])&&Table[Invoice_Date]=PrevDate))

Best Regards,

Amy 

Community Support Team _ Amy

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

[Mariusz · Accepted solution]

Hi @cong_nguyen_acc 

Try this 

VAR = 
VAR __date = 'Table'[Invoice_Date]
VAR __filterTable = ALLEXCEPT( 'Table', 'Table'[SKU_ID] )
VAR __previousDate = 
    CALCULATE(
        MAX( 'Table'[Invoice_Date] ),
        __filterTable,
        'Table'[Invoice_Date] < __date
    )
VAR __previousAmt = 
    CALCULATE(
        SUM('Table'[Amount] ),
        __filterTable,
        'Table'[Invoice_Date] = __previousDate
    )
RETURN IF( NOT ISBLANK( __previousAmt ), 'Table'[Amount] - __previousAmt )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak]

@cong_nguyen_acc , Create new columns like

Last Date = maxx(Filter(Table, Table[SKU_ID]= earlier(Table[SKU_ID]) && [Invoice_Date]<earlier([Invoice_Date])),[Invoice_Date])
Diff = [Amount] - sumx(Filter(Table, Table[SKU_ID]= earlier(Table[SKU_ID]) && [Invoice_Date]=earlier([Last Date])),[Amount])

[az38]

Hi @cong_nguyen_acc 

try a measure 

variance =
var _prevDate = CALCULATE(MAX(Table[Invoice_Date]), FILTER(ALLEXCEPT(Table, Table[SKU_ID]), Table[Invoice_Date] < SELECTEDVALUE(Table[Invoice_Date])) )
RETURN
SELECTEDVALUE(Table[AMOUNT]) - 
CALCUlATE(MAX(Table[AMOUNT]), ALLEXCEPT(Table, Table[SKU_ID]), Table[Invoice_Date]=_prevDate )