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

     

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.