Variance between weeks

[samc_26]

Hi all, I've been googling for this and found some code from this forum but I couldn't get it to work on my report. I've got a table with the following columns:

• Week commencing
• Area
• Week number
• Year

I'm trying to get it to produce a new column showing variance between the weeks but I'm not getting very far. 

I need it to look like this:

Week commencing	Orders	Area	Week no.	Variance
01/04/2024	100	North	1	0
01/04/2024	75	East	1	0
01/04/2024	93	South	1	0
01/04/2024	55	West	1	0
08/04/2024	88	North	2	-12
08/04/2024	39	East	2	-36
08/04/2024	92	South	2	-1
08/04/2024	45	West	2	-10

Any help greatly appreciated! Thank you for reading!

[kushanNa · Accepted solution]

Hi @samc_26 

Please try this Calculated column 

VariCC = 
VAR CurrentWeek = 'YourTable1'[Week no.]
VAR CurrentYear = YEAR('YourTable1'[Week commencing])
VAR CurrentArea = 'YourTable1'[Area]

VAR PrevWeekOrders =
    CALCULATE(
        MAX('YourTable1'[Orders]),
        FILTER(
            'YourTable1',
            'YourTable1'[Week no.] = CurrentWeek - 1 &&
            YEAR('YourTable1'[Week commencing]) = CurrentYear &&
            'YourTable1'[Area] = CurrentArea
        )
    )

RETURN
IF(
    ISBLANK(PrevWeekOrders),
    0,
    'YourTable1'[Orders] - PrevWeekOrders
)

[samc_26]

Hi @kushanNa yes this works, amazing, thank you so much!!!

[rosha_rosha]

Hi samc_26,

Solution: Calculated Column using DAX

Week on Week Variance =
VAR CurrentWeek = 'Table'[Week no.]
VAR CurrentArea = 'Table'[Area]
VAR CurrentOrders = 'Table'[Orders]

VAR PreviousOrders =
CALCULATE(
MAX('Table'[Orders]),
FILTER(
'Table',
'Table'[Week no.] = CurrentWeek - 1 &&
'Table'[Area] = CurrentArea
)
)

RETURN
IF(ISBLANK(PreviousOrders), BLANK(), CurrentOrders - PreviousOrders)

What This Does:

• It looks up the previous week's orders for the same Area.

• Then it subtracts that from the current week's orders.

• If there's no previous week (e.g., Week 1), it returns BLANK().

[samc_26]

Hi @rosha_rosha  thank you for looking into this, I've tried it but unfortunately it's not working the math out right, there may be something I missed with the detail so I've screen shotted the data now and highlighted one area to use as a quick comparison if you have any more ideas?

Could it be a confusion due to repeating week numbers? I have several years worth of data so there will be repeats...

Some of the column names are slightly different to what I described earlier but not by much. Thank you