I understand. Sorry, I missed this part of your problem. I can see that it will be visually misrepresented.
So you need to do something else. This method can be applied to both measures and columns.
- Create your measure: OriginalMeasure = SomeMeasure
- Create a measure, which defines blanks: MeasureWithBlanks = IF(ISBLANK([OriginalMeasure]);"";[OriginalMeasure])
- Create your moving average measure: OriginalMovingAvg = CALCULATE([OriginalMeasure]; DATESINPERIOD(Table1[Date];LASTDATE(Table1[Date]);-3;DAY))
- Create a measure, which excludes blanks: MovingAvg = IF(ISBLANK([MeasureWithBlanks]);"";[Moving average1 of Value])
This looks a little silly if you plot it in a trend-visualization (see picture below), because the measure doesn't calculated anything for the missing day. You can go to format and change the x-axis to continous. This will give a smoother interpretation, but it will misrepresent the x-axis.
Let me know if this solves your problem.
Thanks for you answer,
I solved it already in different way, but i'll save your solution for any case.
The solution that worked for me is:
adding incremental column to the date, which gives id only to the trading days, and another column which is the id - 3 (for the moving average).
then add a measure:
AVERAGEX(FILTER(ALL(DimDate),[Rank] > MAX(DimDate[RankPrev3]) && [Rank] <= MAX(DimDate[Rank])), [AverageColumn])
I'm glad you solved it. Clever way to define which days has values and which hasn't. Could I ask you to choose either one of our solutions as an "accepted solution" for the thread, so we can help people with similar challenges?
Best of luck in the future