User Profile
khenning
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Lookup Visual Calculation not working correctly
Gurus - I am in need of assistance. Hopefully the table above is showing up. I'm working on a matrix visual, each month going across with various details in each row. I need to create a field, ActualInvCalc, for each month that is based on this calculation, prior month's actual inv plus current month's actual sales minus current month's actual prod. Using the example data above for 7/1, it would be 6/1's Actual Inv plus 7/1's Actual Prod minus 7/1's Actual Sales. ex. 1243000 + 65835000 - 2436500 = 64641500. Then for the following month, 8/1, I need to start with what was calculated for 7/1 plus 8/1's Actual Prod minus 8/1's Actual Sales. I'm using this calculation - ActualInvCalc = LOOKUP([Actual Inv], [MTHYR], PREVIOUS([MTHYR])) + [Actual Prod] - [Actual Sales] It's working for the 1st month, 7/1, but it's not for the other months. For 8/1, it's still going back to start with 6/1's Actual Inv instead of what was the result for 7/1. This can be done in excel, but my users want to see this in PBI. I've never used LOOKUP before, so any suggestions would be greatly appreciated. TIA!!!!
