Forum Discussion
21-day Moving Average on a Measure
I am trying to create a 21-day moving average on a measure called 'Total Voy Intake'.
I have a data set of export volumes called 'guinea'.
The main table has the structure:
| Load Date | Load Port | Voy Intake | Vessel Name | Vessel DWT | Commodity | Vessel Type |
Each row in the data set represents a vessel loading, so if there are 10 vessels on 1st Jan 2020, then there are 10 rows with 'Load Date' as 01-Jan-2020. Some days, there are 0 loadings but my program auto-fills these dates with a row that has 0 for 'Voy Intake'.
A sample of a 0 row is:
| 2019-11-03 | Port Kamsar | 0.0 | Unknown | 0.0 | n/a | Unknown |
I created a measure called 'Total Voy Intake' and I use this:
Guinea Total Voy Intake =
COALESCE(
SUM(Guinea[Voy Intake]),
0
)
I think this helps me sum up the total voy intake for each day. (Please correct me if wrong)
'Load Date' has a relationship to the 'Date' column in my DateTable:
I then want to create a 21-day moving average on the Total Voy Intake.
Guinea MA =
AVERAGEX(
DATESINPERIOD(
DateTable[Date],
MAX(DateTable[Date]),
-21,
DAY
),
COALESCE([Guinea Total Voy Intake], 0) -- Treat blank data as 0
)
It is calculating right for every row but whenever the Total Voy Intake row is 0, the calculation goes wrong.
Here is a screengrab of what I am seeing now, with the right-most column to be what I EXPECT TO SEE:
For some reason, whenever Total Voy Intake is 0, the 21day-MA calculates to the same value of 205,380.
Why does this happen and what can I change to get a proper Moving Average on a Measure?
Hi all,
Found the issue and fixed it:
When consolidating the data, I enter "0" rows for dates that do not have any intakes.
Then on Power BI I use the COALESCE function with 0 again.
The PowerBI step actually can handle dates that do not have data without requiring the "0" rows.
The fix was I do not manually populate empty dates with "0" rows. I pull raw data as it is, import it into BI, and stick to the same Total Voy Intake measure as above.
3 Replies
johnt75Super User
I think values of 0 are automatically stripped out. Try
Guinea MA = VAR NumDays = 21 VAR DatesToUse = DATESINPERIOD ( DateTable[Date], MAX ( DateTable[Date] ), - NumDays, DAY ) VAR Result = DIVIDE ( CALCULATE ( [Guinea Total Voy Intake], DatesToUse ), NumDays ) RETURN ResultThanks for the reply from johnt75 , please allow me to provide another insight:
Hi zacharylwy ,You can try the following dax:
Guinea MA = var _21day= MAX('DateTable'[Date])-21 return AVERAGEX( FILTER(ALLSELECTED('DateTable'), 'DateTable'[Date]>=_21day&&'DateTable'[Date]<=MAX('DateTable'[Date])),[Guinea Total Voy Intake])Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi all,
Found the issue and fixed it:
When consolidating the data, I enter "0" rows for dates that do not have any intakes.
Then on Power BI I use the COALESCE function with 0 again.
The PowerBI step actually can handle dates that do not have data without requiring the "0" rows.
The fix was I do not manually populate empty dates with "0" rows. I pull raw data as it is, import it into BI, and stick to the same Total Voy Intake measure as above.
