Forum Discussion

khenning's avatar
khenning
Frequent Visitor
9 months ago

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 fi...
Need Help
MFelix's avatar
MFelix
Super User
9 months ago

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's avatar
khenning
Frequent Visitor
9 months ago

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's avatar
    v-hjannapu
    Community Support
    9 months ago

    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's avatar
      khenning
      Frequent Visitor
      9 months ago

      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's avatar
        v-hjannapu
        Community Support
        9 months ago

        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.