Calculating Average Monthly Sales for a Specific Product

Sure, let's break this task down into two steps:

• First, we need to use CALCULATE to create a new context for our calculation. This will allow us to compute the sales amount for a specific product over a specific period of time.

• Second, we use AVERAGEX to iterate over the months in our modified context, calculating the average monthly sales amount.

Let's consider that you have a Sales table which has columns: SaleDate, ProductID, Amount (sales amount).

AverageMonthlySales = 
CALCULATE(
    AVERAGEX(
        VALUES(Sales[SaleDate]), 
        [TotalSales]
    ),
    FILTER(
        ALL(Sales),
        Sales[ProductID] = "YourProductID" &&
        Sales[SaleDate] >= DATE(2020,1,1) &&
        Sales[SaleDate] <= DATE(2020,12,31)
    )
)

This DAX expression is written in DAX (Data Analysis Expressions) language.

Let's break the code down:

1. We're using CALCULATE to change the context for the calculation inside.
2. AVERAGEX is an iterator function, which means it steps row-by-row through the table specified in its first argument (VALUES(Sales[SaleDate]), a list of all dates when the sales were made), and evaluates the expression given in the second argument ([TotalSales], the sales amount per date).
3. We're using FILTER function to modify the context in the CALCULATE to only include sales for a specific product (Sales[ProductID] = "YourProductID") over a specific period (Sales[SaleDate] >= DATE(2020,1,1) and Sales[SaleDate] <= DATE(2020,12,31)).

In this case, the measure [TotalSales] calculates the total sales per date.

Note that you need to replace "YourProductID" with your actual Product ID, and you can replace the dates for your specific period.

Make sure to create the correct relationships between your tables in your data model and, also, do not forget to replace column names and table names with yours.

Enterprise DNA Platform offers an extensive variety of courses and tutorials on DAX where you can learn more about CALCULATE, AVERAGEX, context modification, and many other advanced DAX concepts.