Forum Discussion

psmits's avatar
psmits
New Member
4 years ago
Solved

Moving Average as a Measure (without dates) with Parameter

Hello, I'm trying to create a moving average with a data set that does not have dates.  I'm able to successfully create one as a custom column per this forum post, but cannot get it working as a measure, and would much prefer to use a measure instead. Partly due to calculation speed on what is a million+ set of rows.

 

I'd also like to add a "what-if parameter" to the dax so that users can change the number of rows included in the moving average. 

 

Below is the example data set.  I've created two custom columns and two measures:

  1. A custom column of just the moving average (working fine, but VERY slow to calculate on 1MM+ data sets)
  2. A custom column of moving average with embedded parameter  (not working)
  3. A measure of moving average (not working)
  4. A measure of moving average with embedded parameter  (not working)

=>  All of the above four calculations should be returning the same numbers (like the first custom column) but are not.

 

 

Would it be helpful to upload the pbi file?  Not seeing an option for that while building this post.

 

I'm very new to Power BI and any help/pointing me in the right direction would be much appreciated.

Cheers.

  • To start with, the simplest measure needs to have the correct table in the FILTER.  This is normally done with ALL(TableName) .

    This fix applies to the other measures as well.

    Let me know about the the performance if you get it working.

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion

    To start with, the simplest measure needs to have the correct table in the FILTER.  This is normally done with ALL(TableName) .

    This fix applies to the other measures as well.

    Let me know about the the performance if you get it working.

  • Thanks HotChilli !  Simply encapsulating my table (named 1652303080) within the ALL function in the FILTER  fixed my issues.  For posterity's sake, here's what I ended with: