Forum Discussion
Lookup Visual Calculation not working correctly
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?
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 .
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?
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.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.
