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 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 commencingOrdersAreaWeek no.Variance
01/04/2024100North

1

0
01/04/2024

75

East10
01/04/202493South10
01/04/202455West10

08/04/2024

88North2-12
08/04/202439East2-36
08/04/202492South2-1
08/04/202445West2-10

 

Any help greatly appreciated! Thank you for reading!

 

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
)

     

     

4 Replies

  • kushanNa's avatar
    kushanNa
    Super User
    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