21-day Moving Average on a Measure

[zacharylwy]

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?

[zacharylwy · Accepted solution]

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. 

[Anonymous]

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

[johnt75]

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
    Result