Moving Average ignoring Dates without transaction

[JoRose]

Hello DAX Community,

i have the following issue: My Datamodel has the three tables

1. DimMeasurement -> Contains different measurement types 
2. DimDate 
3. FactMeasure -> Every measurement one row, one of the two measurement types from DimMeasurement

I want to calculate the moving average over the measured variation. The variation is measured severel times per day. On the same day different measurement types can occur, in my example scenario length and width measurements.

Usually nothing is measured on weekends. So the moving average needs to distinguish between the measurement types and it needs to ignore dates without measurement (not necessarily weekend but also a week day without measurement). I have uploaded the example datamodel to my OneDrive DAX_MovingAverage.pbix .
I hope one of you can help me out.

Thanks a lot

[lbendlin]

 it needs to ignore dates without measurement (not necessarily weekend but also a week day without measurement)

That's a UX red flag.

But if you must - use one of the new Window functions. They are based on the current filter context and give you what you need "for free".

[JoRose]

Hello Ilbedlin, 

in how far is this a UX red flag?

I need the moving average being calculated right. And as i know this means that days without transaction (in my case a measurement on a component), need to be ignored. Otherwise they drag the result of the moving average down although there were company holidays and nothing was measured.
correct me if i am wrong.

Could you, based on my file, give an example how a window function could solve that task?

[lbendlin · Accepted solution]

In your original post you said 

 but also a week day without measurement

You didn't mention holidays, or describe the overall context.  That led me to assume that you have regular workdays without data.  In such a case I would not want to ignore that fact, but rather highlight the impact.

Please indicate the window size and position for your moving average (for example "three days before to three days after").  Please also consider adding a workday flag to your dates table, rather than using a measure.

[JoRose]

Helo Ibendlin,

your solution works perfectly. Just changed "COUNTROWS" with "AVERAGEX" in combination with the measure "Variation".

Thanks a lot!! 

[JoRose]

Helo Ibendlin,

thanks for your answer so far. I havent come to test it yet.