Lookup Visual Calculation not working correctly

[khenning]

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!!!!

[v-hjannapu]

Hi @khenning,

I would also take a moment to thank @MFelix  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @khenning,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support team.

[MFelix]

Hi @khenning ,

In order to help you out there is the need to have more context. But the lookup function is not the best option for this type of calculation. Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[khenning]

I did add a mockup of my data.  Did it not show up?

I was trying the lookup but, I agree, that's not the best way.

Basically, I have 18 months worth of data for the columns in my matrix.  I need to have a field in a row be equal to the previous month's data in that same field plus other data.  And then the same for the next month. 

In excel, the formula for month 1 is =C24+D14-D6, then month 2 is =D24+E14-E6, and so on.  

Does that help?

[MFelix]

Hi @khenning ,

The image with the data is there however from the image only we cannot perecívei the data you have, relationship, tables and everything that give context.

For examples do you have a calendar table?  Does your different values on rows are categories on a table or actual values on your fact?

If you are able to share a mockup file it would be apprrciated .

[khenning]

I apologize for any confusion.  We use an Oracle database and in 1 view that's in the dataset is where all the data is.  I also have a date view so that if we need to filter it other ways, but that's not the plan.  

The dates are going to be on rolling 21 months, 3 months in the past and 18 months in the future.  The dataset is refresh every morning to make sure we have the latest data.

The basic of my ask is this...Is there a way where we can reference a prior month's calculated field and carry it forward to the next month and use it in a calculated field and keep that going on in the future?

[v-hjannapu]

Hi @khenning,
you want to carry forward the previous month’s calculated value and use it for the next month, like how we do in Excel.In Power BI, this can’t be done directly because DAX doesn’t calculate row by row like Excel. The LOOKUP function will only pick the data from your table, not the value that was calculated in the previous month.To get the same result, we’ll need to create a proper date table and use a running total type of formula with CALCULATE and FILTER.

please go through with the below document hope it may resolve your Issue.   https://learn.microsoft.com/en-us/dax/runningsum-function-dax

Hope this helps if you have any queries we are  happy to assist you further.
Regards,
Community Support Team.

[khenning]

Thank you for the information.  I'll just work out how to get the data within the view in Oracle before I load it into PBI.

[v-hjannapu]

Hi @khenning,
Thank you for the Update If you face any issue later while loading or checking data in Power BI, feel free to ask here  we will help you out.

Regards,
Community Support Team.