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
)

     

     

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
)

 

 

  • samc_26's avatar
    samc_26
    Helper IV
    1 year ago

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