Forum Discussion
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...
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:
mariussve1
Solution Sage
Solution SageHi 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: