Forum Discussion

AllG's avatar
AllG
Frequent Visitor
9 years ago
Solved

Get This Week - Last Week Value

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 Boo...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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's avatar
AllG
Frequent Visitor
9 years ago

Awesome!!!  :smileyhappy:

 

thank you very much Yuliana Gu