Forum Discussion

Ramfeb27's avatar
Ramfeb27
Regular Visitor
1 year ago
Solved

Revenue lost in Next year

Hi Everyone, please help me on the below use case   I want to calculate Revenue lost in the next year value. In the below sample data, in the Lost column, whichever record is marked as 1 needs to m...
Reports
  • mariussve1's avatar
    mariussve1
    1 year ago

    Hi Ramfeb27 ðŸ™‚

    You can try this measure:

    Lost Revenue Measure = 
VAR __PrevYear =
    SELECTEDVALUE ( Demo[Year] ) - 1
VAR __CustID =
    SELECTEDVALUE ( Demo[Cus#] )
VAR __PrevRevenue =
    LOOKUPVALUE ( Demo[Revenue], Demo[Year], __PrevYear, Demo[Cus#], __CustID )
RETURN
    IF ( SELECTEDVALUE ( Demo[Lost] ) = 1, __PrevRevenue )

     

    Or if you prefer a calculated column:

    Lost Revenue Calculated Column = 
    VAR __PrevYear = Demo[Year] - 1
    VAR __CustID = Demo[Cus#]
    VAR __PrevRevenue =
        LOOKUPVALUE ( Demo[Revenue], Demo[Year], __PrevYear, Demo[Cus#], __CustID )
    RETURN
        IF ( Demo[Lost] = 1, __PrevRevenue )

     

    I just copied your tabel above and named it "Demo", and this is the result:

     

SamanthaPuaXY's avatar
SamanthaPuaXY
Icon for Helper II rankHelper II
1 year ago

Hi Ramfeb27 I'm not quite getting the logic that you want with example 2.

Cus#YearRevenueLost
90192020     68,5050
90192021   472,8760
90192022   486,7150
90192023                -0
90192024     89,7250
90202020   486,7150
90202021     68,5051
90202022                -0
90202023   472,8760
90202024   792,5250
90212020   152,8960
90212021   258,4561
90212022   987,4560
90212023   258,7431
90212024   325,6980

 

Ex2: Customer 9021 has two 1's in 2021 & 2023 so his value should be:
(1 * 152,896) = 152,896 -> This takes 2020 Customer 9021
(1 * 258,743) = 258,743 -> Isn't this supposed to take 2022 Customer 9021 which is 987,456?
And if there's the previous year revenue loss of 2022 is including 2023?