Setup

Ensure your Power BI dataset includes sales data with at least the following columns:

• CustomerID
• SalesAmount
• SalesDate

Data Preparation and Loading

1. Load your dataset into Power BI (omit repetitive steps like connecting to data source).

DAX Implementation

// Step 1: Calculate the beginning of the last month
LastMonthStart =
STARTOFMONTH(DATEADD(TODAY(), -1, MONTH))

// Step 2: Calculate the end of the last month
LastMonthEnd =
ENDOFMONTH(DATEADD(TODAY(), -1, MONTH))

// Step 3: Filter sales data for the last month
LastMonthSales =
FILTER(
    Sales,
    Sales[SalesDate] >= [LastMonthStart] && Sales[SalesDate] <= [LastMonthEnd]
)

// Step 4: Calculate total sales per customer for the last month
TotalSalesLastMonth =
CALCULATE(
    SUM(Sales[SalesAmount]),
    LastMonthSales
)

// Step 5: Identify repeat customers
RepeatCustomers =
FILTER(
    DISTINCT(Sales[CustomerID]),
    COUNTROWS(
        FILTER(
            Sales,
            Sales[CustomerID] = EARLIER(Sales[CustomerID])
        )
    ) > 1
)

// Step 6: Calculate total repeat customers' sales
RepeatCustomersSales =
CALCULATE(
    [TotalSalesLastMonth],
    RepeatCustomers
)

// Step 7: Calculate count of repeat customers
RepeatCustomersCount =
CALCULATE(
    COUNTROWS(RepeatCustomers)
)

// Step 8: Calculate average sales per repeat customer
AverageSalesPerRepeatCustomer =
DIVIDE(
    [RepeatCustomersSales],
    [RepeatCustomersCount]
)

Use these measures within your Power BI report to visualize or utilize in further analysis. This computes the average sales per repeat customer for the last month.

DAX Formula Implementation for Average Sales per Repeat Customer

Calculate Total Sales per Customer

TotalSales = SUM(Sales[SalesAmount])

Calculate Total Sales in the Last Month

SalesLastMonth = 
CALCULATE(
    [TotalSales], 
    DATESINPERIOD(
        Sales[SaleDate], 
        MAX(Sales[SaleDate]), 
        -1, 
        MONTH
    )
)

Identify Repeat Customers

RepeatCustomers = 
CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]), 
    FILTER(
        Sales, 
        CALCULATE(
            COUNTROWS(Sales), 
            ALLEXCEPT(Sales, Sales[CustomerID])
        ) > 1
    )
)

Calculate Total Sales for Repeat Customers in the Last Month

SalesLastMonthRepeatCustomers = 
CALCULATE(
    [SalesLastMonth], 
    FILTER(
        DISTINCT(Sales[CustomerID]), 
        [RepeatCustomers]
    )
)

Calculate Average Sales per Repeat Customer in the Last Month

AvgSalesPerRepeatCustomerLastMonth = 
DIVIDE(
    [SalesLastMonthRepeatCustomers], 
    [RepeatCustomers], 
    0
)

Apply the above measures in your DAX environment to obtain the analysis of average sales per repeat customer within the last month. Ensure your data is loaded and properly prepared before implementing these measures.

DAX Implementation for Calculating Average Sales per Repeat Customer

Define Calculations

1. Repeat Customers Calculated Column: Checks if a customer has made more than one purchase in the last month.

RepeatCustomers = 
VAR CustomerID = Sales[CustomerID]
VAR LastMonthSales = 
    CALCULATETABLE(
        Sales,
        DATESINPERIOD(Sales[Date], LASTDATE(Sales[Date]), -1, MONTH),
        Sales[CustomerID] = CustomerID
    )
RETURN 
    IF(COUNTROWS(LastMonthSales) > 1, 1, 0)

2. Total Sales for Repeat Customers Measure: Aggregates sales values for customers flagged as repeat customers.

TotalSalesRepeatCustomers = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        Sales,
        Sales[RepeatCustomers] = 1
    )
)

3. Average Sales per Repeat Customer Measure: Calculates the average sales by dividing total sales by the count of unique repeat customers.

AvgSalesPerRepeatCustomer = 
VAR RepeatCustomerCount = 
    CALCULATE(
        DISTINCTCOUNT(Sales[CustomerID]),
        Sales[RepeatCustomers] = 1
    )
RETURN
    IF(
        RepeatCustomerCount > 0,
        [TotalSalesRepeatCustomers] / RepeatCustomerCount,
        BLANK()
    )

Applying the Measures

After defining these calculations, you can apply the AvgSalesPerRepeatCustomer measure to your reporting visuals to display the average sales per repeat customer within the last month.

These measures assume that you have a table named Sales with at least columns for CustomerID, SalesAmount, and Date.

Use these DAX definitions in your Power BI or Analysis Services models.

DAX Code for Average Sales per Repeat Customer within the Last Month

// Define the start and end date for the last month
VAR StartDate = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
VAR EndDate = EOMONTH(TODAY(), -1)

// Filter sales data within the last month
VAR SalesLastMonth = 
    CALCULATETABLE(
        Sales,
        Sales[Date] >= StartDate && Sales[Date] <= EndDate
    )

// Identify repeat customers
VAR RepeatCustomerSales = 
    CALCULATETABLE(
        SalesLastMonth,
        FILTER(
            SalesLastMonth,
            CALCULATE(COUNTROWS(Sales), ALLEXCEPT(Sales, Sales[CustomerID])) > 1
        )
    )

// Calculate the average sales per repeat customer
VAR AvgSalesPerRepeatCustomer = 
    AVERAGEX(
        VALUES(RepeatCustomerSales[CustomerID]),
        CALCULATE(SUM(Sales[Amount]))
    )

RETURN AvgSalesPerRepeatCustomer

Key Points:

• StartDate and EndDate are used to define the time range for the last month.
• SalesLastMonth filters the sales data to include only sales within the last month.
• RepeatCustomerSales identifies customers who have made more than one purchase in the last month.
• AvgSalesPerRepeatCustomer calculates the average sales per repeat customer.

Creating Reports and Sharing Insights

Objective: Calculate Average Sales per Repeat Customer within the Last Month using DAX

1. Calculating Repeat Customers

RepeatCustomers = 
CALCULATETABLE(
    Customers,
    COUNTROWS(Sales) > 1
)

2. Filtering Sales in the Last Month

SalesLastMonth = 
CALCULATETABLE(
    Sales,
    DATESINPERIOD(
        Sales[OrderDate],
        MAX(Sales[OrderDate]),
        -1,
        MONTH
    )
)

3. Total Sales for Repeat Customers in the Last Month

TotalSalesRepeatCustLastMonth = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        SalesLastMonth,
        Sales[CustomerID] IN VALUES(RepeatCustomers[CustomerID])
    )
)

4. Distinct Repeat Customers in the Last Month

DistinctRepeatCustomersLastMonth = 
CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]),
    FILTER(
        SalesLastMonth,
        Sales[CustomerID] IN VALUES(RepeatCustomers[CustomerID])
    )
)

5. Average Sales per Repeat Customer in the Last Month

AvgSalesPerRepeatCustLastMonth = 
DIVIDE(
    [TotalSalesRepeatCustLastMonth],
    [DistinctRepeatCustomersLastMonth]
)

6. Creating the Report

1. Add AvgSalesPerRepeatCustLastMonth to your report.
2. Utilize the Power BI / PowerPivot visualization tools to share insights.

FinalReport = 
AvgSalesPerRepeatCustLastMonth

Sharing Insights

• Publish the report to the intended platform.
• Ensure that stakeholders have access to the insights through shared reports or dashboards.