Creating dynamic sales data is a common requirement when building reports or dashboards in Power BI. In this article, we will walk through the process of generating a sales table for a 10-year period (from 2015 to 2025), with a focus on monthly sales, including order date, payment date, delivery date, price, shipping cost, and more.

How to Create a 10-Year Sales Table in Power BI Using DAX

Creating dynamic sales data is a common requirement when building reports or dashboards in Power BI. In this article, we will walk through the process of generating a sales table for a 10-year period (from 2015 to 2025), with a focus on monthly sales, including order date, payment date, delivery date, price, shipping cost, and more.

We will also explore how to apply DAX formulas to manipulate and analyze the data, such as checking if a delivery was late based on the difference between the payment and delivery dates.

Step 1: Creating the Base Sales Table

We start by creating the base sales table that generates a random sales order for each of the 10 years. We will assume 30 sales per month over the span of 10 years, which totals 3,600 sales (12 months × 10 years × 30 sales/month).

DAX Formula for Base Sales Table

Here’s the DAX formula to generate the table with the necessary columns:

SalesTable =  
VAR StartDate = DATE(2015, 1, 1)  
VAR EndDate = DATE(2025, 12, 31)  
VAR TotalSales = DATEDIFF(StartDate, EndDate, MONTH) * 30  

RETURN  
    ADDCOLUMNS(  
        GENERATESERIES(1, TotalSales, 1),  
        "Order Date",  
            DATE(2015 + INT(([Value] - 1) / 360),  
                 MOD([Value] - 1, 12) + 1,  
                 RANDBETWEEN(1, 28)),  
        "Payment Days", RANDBETWEEN(1, 5),  
        "Delivery Days", RANDBETWEEN(2, 10),  
        "Price",  
            ROUND(RANDBETWEEN(200, 1200) * 1.0, 2),  
        "Shipping Cost",  
            ROUND(RANDBETWEEN(10, 50) * 1.0, 2)  
    )

Explanation of the Formula

• Start Date and End Date: These are the boundaries for the sales data (from 2015 to 2025).
• Total Sales: We calculate the total number of sales based on the months (30 sales/month × total number of months).
• Order Date: A random date is generated for each order within each month.
• Payment Days: A random number (between 1 and 5) is added to the order date to simulate when the payment is made.
• Delivery Days: A random number (between 2 and 10) is added to the payment date to simulate when the product is delivered.
• Price: A random price is assigned to each sale (between $200 and $1200).
• Shipping Cost: A random shipping cost is assigned to each sale (between $10 and $50).

Step 2: Calculating Payment and Delivery Dates

Next, we calculate the Payment Date and Delivery Date based on the order date and the number of days specified in the Payment Days and Delivery Days columns.

We can use the following DAX formulas to create the calculated columns:

Payment Date Formula

Payment Date = 'SalesTable'[Order Date] + 'SalesTable'[Payment Days]

Delivery Date Formula

Delivery Date = 'SalesTable'[Payment Date] + 'SalesTable'[Delivery Days]

Step 3: Checking for Late Deliveries

Now, we want to add a column that checks if the delivery was made more than 5 days after payment. If the difference between the Payment Date and the Delivery Date is greater than 5 days, the column will return TRUE, indicating the delivery is late.

Late Delivery Check Formula

Is Late = IF(DATEDIFF('SalesTable'[Payment Date], 'SalesTable'[Delivery Date], DAY) > 5, TRUE, FALSE)

Explanation

• DATEDIFF: This function calculates the difference between two dates, in this case, between the Payment Date and Delivery Date.
• If the difference is greater than 5 days, it returns TRUE, otherwise FALSE.

Step 4: Validating the Sales Data

Once the table is created, it’s important to validate that the sales data matches the expected output. You can do this by counting the number of sales entries.

Sales Count Formula

Total Sales = COUNT('SalesTable'[Order Date])

This should return 3,600 records (30 sales per month × 12 months × 10 years).

Conclusion

In this article, we demonstrated how to generate a dynamic sales table in Power BI for a 10-year period, using DAX formulas to create random sales data and analyze delivery performance. The key steps included:

• Generating the base sales table.
• Calculating payment and delivery dates.
• Creating a formula to check if a delivery is late.
• Validating the data using a simple sales count.

These techniques will help you generate robust sales datasets for various use cases and perform detailed analyses in Power BI.