Forum Discussion
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.
Rows: Year column
Month column
Values: Projectmutaties.Verkoop-bedrag column
the Rolling average column shows exactly the same data as the 'Projectmutaties.Verkopen-bedrag' column
| Year | Projectmutaties.Verkopen-bedrag | Rolling average |
| 2022 | 6.521 | 6.521 |
| Jan 22 | 2.250 | 2.250 |
| Feb 22 | 1.684 | 1.684 |
| Mar 22 | 1.487 | 1.487 |
| Apr 22 | 600 | 600 |
| etc | 500 | 500 |
| Total | 6.521 | 6.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
Anonymous , make sure year and month are coming from date table. Measure is correct, try like
CALCULATE(
AVERAGEX(
VALUES('Date Table'[Year-Month]);
AVERAGE([Projectmutaties.Verkoop-bedrag])
);DATESINPERIOD('Date Table'[Date]; LastSelectedDate; -NumOfMonths; MONTH ) )Join with date table should be single directional
It can be because any of the 5 reason
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
1 Reply
- amitchandakSuper User
Anonymous , make sure year and month are coming from date table. Measure is correct, try like
CALCULATE(
AVERAGEX(
VALUES('Date Table'[Year-Month]);
AVERAGE([Projectmutaties.Verkoop-bedrag])
);DATESINPERIOD('Date Table'[Date]; LastSelectedDate; -NumOfMonths; MONTH ) )Join with date table should be single directional
It can be because any of the 5 reason
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4