topic Re: Moving Average ignoring Dates without transaction in DAX Commands and Tips

Moving Average ignoring Dates without transaction

JoRose — Sat, 02 Sep 2023 04:26:44 GMT

Hello DAX Community,

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

DimMeasurement -> Contains different measurement types
DimDate
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

Re: Moving Average ignoring Dates without transaction

lbendlin — Sat, 02 Sep 2023 20:21:11 GMT

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".

Re: Moving Average ignoring Dates without transaction

JoRose — Sun, 03 Sep 2023 16:47:12 GMT

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?

Re: Moving Average ignoring Dates without transaction

lbendlin — Sun, 03 Sep 2023 19:45:51 GMT

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.

Re: Moving Average ignoring Dates without transaction

JoRose — Wed, 13 Sep 2023 13:25:54 GMT

Helo Ibendlin,

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

Re: Moving Average ignoring Dates without transaction

JoRose — Thu, 14 Sep 2023 08:36:31 GMT

Helo Ibendlin,

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

Thanks a lot!!