Calculate variance between 2 weeks

[Krisvenkata11]

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	Sales	Variance
1	6	6
2	5	-1
3	8	3
4	7	-1
5	4	-3
6	3	-1
7	2	-1
8	0	-2
9	10	10
10	12	2

[Anonymous · Accepted solution]

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.

[Jos_Woolley]

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