Forum Discussion

cong_nguyen_acc's avatar
cong_nguyen_acc
Frequent Visitor
6 years ago
Solved

Calculate variance from previous date

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_IDInvoice_DateAmount
A16/05/2019                 16,320
A14/06/2019                 35,040
A15/06/2019                 17,520
A03/12/2019                 29,760
A07/02/2020                 32,640
A31/03/2020                 32,640
A08/04/2020                 32,640
A10/04/2020                 32,640
B15/05/2019                 95,340
B16/05/2019               572,040
B17/05/2019               190,680
B26/06/2019                 85,260
B28/06/2019               255,780
B22/07/2019                 85,260
B23/10/2019         27,300,000

 

Thanks all,

 

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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 )
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

     

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

     

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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.