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 Week Sal...
  • 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.

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.