Get This Week - Last Week Value

[AllG]

Hi,

How do i calculate values based on criteria referring single column, for example

Column 1 is week number and column 2 is Booking Amt

Now how can i get variance of booking amt like Booking Amt for Week 1 - Booking Amt for Week 2

thank you!!

[v-yulgu-msft · Accepted solution]

Hi @AllG,

Add an index column in Query Editor mode. Before adding an index column, please sort data based on week number column.

Close and apply all these operations. But if [Column1] values are whole number type rather than text, there is no need to do above changes.

In design surface, add a calculated column using below formula:

variance of booking amt =
IF (
    'booking amt'[Column1] = "Week1",
    BLANK (),
    LOOKUPVALUE (
        'booking amt'[Column2],
        'booking amt'[Index], 'booking amt'[Index] - 1
    )
        - 'booking amt'[Column2]
)

Best regards,
Yuliana Gu

[AllG]

Awesome!!! 

thank you very much Yuliana Gu