Forum Discussion

sidvix917's avatar
sidvix917
Icon for Helper I rankHelper I
3 years ago

What is wrong with this Rolling average (3months).

I am trying to fix the below measure. It is giving the same price value and not the rolling average.

What I am getting:

 

What I desire:

 



Rolling average (3months) =
var Date_till_which_data_is_available = MAXX(ALL(Dates),MAX(Query1[observations.date]))
var Total_Sales_Values = CALCULATE(sum(Query1[observations.value]),USERELATIONSHIP(Query1[observations.date],Dates[Date]))
Var Last_Date = CALCULATE(MAX(Dates[Date]), FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date]) && Total_Sales_Values<> 0))
Var sales_value = SUM(Query1[observations.value])
var Last_Non_Blank_Value = CALCULATE(sales_value,FILTER(ALL(Dates),Dates[Date] = Last_Date))
var Last_Date_with_Data = if(MIN(Dates[Date])>Date_till_which_data_is_available,BLANK(),Last_Non_Blank_Value)

return CALCULATE(AVERAGEX(VALUES(Dates[Month & Year]),Last_Date_with_Data),DATESINPERIOD(Dates[Date],MAX(Dates[Date]),-3,MONTH))
Need Help

9 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    3 years ago

    We want to help you but your description is too vaugue. Please write it again clearly.

    Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That would be crazy. 😀

    Please just give a simple non technical functional decscription of what you want, then let us suggest the DAX. Thank you.

    Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
    Also provide the example desired output, with a clear description of the process flow.

    Remember not to share private data ... we don't want you to get into trouble. 😧

    Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.

    You will get a quick response if you put time, care and effort into writing clear problem descriptions.

    Vaugue descriptions can waste your time and ourtime.

    Look foward to helping you when the above information is forthcoming

    • sidvix917's avatar
      sidvix917
      Icon for Helper I rankHelper I
      3 years ago

      Hey Speed,

       

      I appreciate your response and I agree.

       

      Therefore I am attaching the sample pbix file of what I want to accomplish.

       

      1) I have data that I pull from a third-party source (API) and load into my Power BI report. In this case, I just used an Excel workbook with the same amount of data.

       

      2) When I put the original prices in the matrix table (not the measure I currently have on there) it shows me gaps in between months because there is no price recorded in that month. Therefore I would take the previous month's price and duplicate it if the month's price is not available.

      3) I want to be able to calculate the 3-month rolling average including the prices that I have copied from the previous month that had a value.

       

      4) I believe I have managed to do all that but receive an error  'visual has exceeded the available resources' . I don't want it to do that because it is important to show that visual. Now one issue that I read about is that my measures are using up too much memory. 


      Would you be able to help me with this? Here is the link to the file:

      https://go.wetransfer.com/t-kDe1NYkiDy

       

      Thank you.

      • sidvix917's avatar
        sidvix917
        Icon for Helper I rankHelper I
        2 years ago

        Does anyone need clarification? Is there a way to solve this?

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    2 years ago

    Please ask your manager or a friend for help on how to write a clear problem description.
    We are unpaid volunteers who are keen to help teach, but we are not here to do your job for you. 

    Please take adequate time and effort to explain problems conisiely and clearly,  rather than dump all your confussing data with vague table and field names and expect us to know what you want. 🙄

     

    • sidvix917's avatar
      sidvix917
      Icon for Helper I rankHelper I
      2 years ago

      What is it that you don't understand? I'm not expecting you to perform my job. I'm looking for assistance. I suppose you are more interested in talking than in helping. I don't require your help, but I appreciate it.

       

      • speedramps's avatar
        speedramps
        Icon for Super User rankSuper User
        2 years ago

        If you took trouble to look at my profile you will see I have received multiple awards for help.
        If you search you will also see I get fantastic feedback for sharing my expert knowledge. I enjoy helping.

        The problem is YOU.
        You cant do your job and are too lazy to ask clear and concise questions.
        You waste time being rude when you could just have easily provided the requested infomration and already got an answer.
        Please try harder or ask a grown-up for help to write a clear decsription and I will be happy to help you.