Forum Discussion

samc_26's avatar
samc_26
Helper IV
1 year ago
Solved

Variance between weeks

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 numbe...
Need Help
  • kushanNa's avatar
    kushanNa
    1 year ago

    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
)

     

     

rosha_rosha's avatar
rosha_rosha
Resolver II
1 year ago

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's avatar
    samc_26
    Helper IV
    1 year ago

    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