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 wit...
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

     

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