DAX: Monthly SMA on hourly data

[chulpanvl]

Hello everyone!

I have a Table with number of visitors at some points in time. It is related to a Calendar table.

I need to plot a histogram with average monthly visitors and add a line with simple moving average sma(3) without aggregating the data first.

I managed to plot a histogram but failing at calculating SMA. A measure I’ve created doesn’t seem to work:

SMA =

    AVERAGEX(

    DATESINPERIOD('Table'[Date],LASTDATE('Table'[Date]), -3, MONTH),

    CALCULATE(AVERAGE('Table'[Sum by Day]))

)

Here is a pbix file to play with.

Hope to hear some advice here.

[lbendlin]

That's not really a histogram. But what's wrong with the SMA formula?  What would you have expected? How did you plan to handle all the days without visitors?

[chulpanvl]

@lbendlin 

you’re right, I meant a column chart.

Initially I have hourly data. I’ve created a calculated column Sum by Day. Then there is a measure avg = AVERAGE('Table'[Sum by Day]), which returns average daily number of visitors per month if plotted as a column chart vs month on x-axis. Now I want to add SMA line to this chart, which is basically an average of an average, if it makes any sense. E.g., for the last 3 months the values for avg measure are 34.17, 53.67, 41.67. So I would expect of SMA to return 43.17 for March 2022. But SMA returns same values as avg measure. What am I doing wrong?

> How did you plan to handle all the days without visitors?

I assume there are no such days.

[V-lianl-msft · Accepted solution]

Hi @chulpanvl ,

By my tests,please refer to the sample pbix:

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chulpanvl]

Hi @V-lianl-msft, thank you for your solution!