Revenue lost in Next year

[Ramfeb27]

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 multiply with the previous year revenue.
Ex: In the below data, Customer 9020 has 1 in the Lost column for 2021. So now, as per the business logic, it needs to multiply with the previous year revenue (1 * 486,715 (prev year revenue) = 486715).

Cus#	Year	Revenue	Lost
9019	2020	68,505	0
9019	2021	472,876	0
9019	2022	486,715	0
9019	2023	-	0
9019	2024	89,725	0
9020	2020	486,715	0
9020	2021	68,505	1
9020	2022	-	0
9020	2023	472,876	0
9020	2024	792,525	0
9021	2020	152,896	0
9021	2021	258,456	1
9021	2022	987,456	0
9021	2023	258,743	1
9021	2024	325,698	0

Ex2: Customer 9021 has two 1's in 2021 & 2023 so his value should be:
(1 * 152,896) = 152,896
(1 * 258,743) = 258,743

So final output will be:

2020	2021	2022
0	(486715 + 152896) = 639611	258,743

Which is the best way, Measure or Calculated Column?

[v-shex-msft]

Hi @Ramfeb27 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[mariussve1 · Accepted solution]

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:

[Sahir_Maharaj]

Hello @Ramfeb27,

Can you please try this approach:

Revenue_Lost_Next_Year = 
VAR PrevYear = SELECTEDVALUE('RevenueTable'[Year]) - 1
VAR Revenue_Lost =
    SUMX(
        FILTER(
            ALL('RevenueTable'),
            'RevenueTable'[Year] = PrevYear && 'RevenueTable'[Lost] = 1
        ),
        'RevenueTable'[Revenue]
    )
RETURN
    Revenue_Lost

[SamanthaPuaXY]

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

Cus#	Year	Revenue	Lost
9019	2020	68,505	0
9019	2021	472,876	0
9019	2022	486,715	0
9019	2023	-	0
9019	2024	89,725	0
9020	2020	486,715	0
9020	2021	68,505	1
9020	2022	-	0
9020	2023	472,876	0
9020	2024	792,525	0
9021	2020	152,896	0
9021	2021	258,456	1
9021	2022	987,456	0
9021	2023	258,743	1
9021	2024	325,698	0

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?