Forum Discussion

Krisvenkata11's avatar
Krisvenkata11
Frequent Visitor
4 years ago
Solved

Calculate variance between 2 weeks

 

I am looking to calculate the dax query  some thing like below(Measure or column). Variance should be the output which is difference the weeks like diff between week2 and week1 is -1

WeekSalesVariance
166
25-1
383
47-1
54-3
63-1
72-1
80-2
91010
10122
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Krisvenkata11 ,

     

    You can create a measure like below to calculate variance between 2 weeks :

     

    Variance =
VAR cur_week =
    MIN ( Sheet1[Week] )
VAR previous_week =
    CALCULATE (
        MAX ( Sheet1[Week] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Week] < cur_week )
    )
VAR cur_sales =
    MIN ( Sheet1[Sales] )
VAR previous_sales =
    CALCULATE (
        MAX ( Sheet1[Sales] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Week] = previous_week )
    )
RETURN
    cur_sales - previous_sales

     

     

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Hi,

    As a measure:

    MyMeasure =
VAR ThisWeek =
    MIN( 'Table'[Week] )
VAR PreviousWeek =
    CALCULATE(
        MAX( 'Table'[Week] ),
        FILTER( ALLSELECTED( 'Table' ), 'Table'[Week] < ThisWeek )
    )
VAR ThisSales =
    MIN( 'Table'[Sales] )
VAR PreviousSales =
    CALCULATE(
        MIN( 'Table'[Sales] ),
        FILTER( ALLSELECTED( 'Table' ), 'Table'[Week] = PreviousWeek )
    )
RETURN
    ThisSales - PreviousSales

    Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Krisvenkata11 ,

     

    You can create a measure like below to calculate variance between 2 weeks :

     

    Variance =
VAR cur_week =
    MIN ( Sheet1[Week] )
VAR previous_week =
    CALCULATE (
        MAX ( Sheet1[Week] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Week] < cur_week )
    )
VAR cur_sales =
    MIN ( Sheet1[Sales] )
VAR previous_sales =
    CALCULATE (
        MAX ( Sheet1[Sales] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Week] = previous_week )
    )
RETURN
    cur_sales - previous_sales

     

     

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.