Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Moving Monthly average

Hi,

trying to compute the monthle moving average of sales.
In my case i have merged two table in the QueryEditor, both fact tables.
Table 1: Containing the facts on invoice number level, and;
Table 2: Containing the facts on invoice line level.
Relation therfore is One (table 1) to Many (Table 2).
I created the merged table as new, called "Merge facturen met mutaties" Dutch for: "Merge invoices with mutations"

I also created a Date table in DAX. Nothing unusal there.
I created a relationship beteween the date table and merged table.
Many (Invoice dates) to One (Date table)

now i would like to compute the monthly moving average of sales over the past 12 months.
I used the syntax (thanks SLQBI) to do so, but unfortunatly it did not work in my case.

Rolling average =
VAR NumOfMonths = 12
VAR LastSelectedDate = MAX( 'Date Table'[Date] )
VAR Period =
    DATESINPERIOD('Date Table'[Date]; LastSelectedDate; -NumOfMonths; MONTH )
VAR Result=
    CALCULATE(
        AVERAGEX(
            VALUES('Date Table'[Year-Month]);
            AVERAGE([Projectmutaties.Verkoop-bedrag])
        );
        Period
    )
VAR LastDateWithSale = MAX('Merge facturen met mutaties'[Projectmutaties.Jaar/periode])
VAR FirsVisibleDate = MIN('Date Table'[Date])
Return
    IF(FirsVisibleDate<=LastDateWithSale;Result)
 
When i create a Matrix with
Rows: Year column 
          Month column
Values: Projectmutaties.Verkoop-bedrag column
            Rolling average column

the Rolling average column shows exactly the same data as the 'Projectmutaties.Verkopen-bedrag' column

YearProjectmutaties.Verkopen-bedragRolling average
20226.5216.521
Jan 222.2502.250
Feb 221.6841.684
Mar 221.4871.487
Apr 22600600
etc500500
Total6.5216.521

 

The strange thing i noticed is that DAX can't find a reference to ([Projectmutaties.Verkoop-bedrag]) when i'm not using AVERAGE() or SUM().
Why is that, because i think that is the thing messing up the calulation.
The Youtube video from SQLBI didnt use the AVERAGE() 


Thanks

 

1 Reply