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

     

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