DAX Function for Weekly Sales Revenue

Purpose

This function calculates and displays total sales revenue grouped by full weeks, with Monday as the start of the week and Sunday as the end.

Code Implementation

WeeklySalesRevenue = 
VAR StartOfWeek = 
    STARTOFWEEK('Sales'[OrderDate], 2) // 2 indicates Monday
VAR EndOfWeek = 
    ENDOFWEEK('Sales'[OrderDate], 1) // 1 indicates Sunday
RETURN
    SUMMARIZE(
        'Sales',
        StartOfWeek,
        EndOfWeek,
        "Total Revenue", SUM('Sales'[SalesAmount])
    )

Explanation

• STARTOFWEEK: This function calculates the start date of the week for each order date, using Monday as the reference day (indicated by 2).
• ENDOFWEEK: This function calculates the end date of the week for each order date, using Sunday as the reference day (indicated by 1).
• SUMMARIZE: This function is used to group the data by calculated start and end week dates and sums up the total sales revenue for each week.

Input Validation

DAX itself does not allow for traditional input validation in the way programming languages do, but ensure that:

• The ‘Sales’ table exists.
• The ‘Sales’ table has the column names OrderDate and SalesAmount.

Commentary

• The function can be directly utilized in a Power BI report or any compatible DAX environment.
• Ensure the 'Sales' data model is refreshed to incorporate the latest sales data before running the function.

Code Usage Example

To display this data in a report visual:

1. Create a new table visual in Power BI.
2. Add the WeeklySalesRevenue measure.
3. Use the columns returned by SUMMARIZE for your rows to create a clear representation of weekly sales revenue.

Note

For further DAX learning and refinement of skills, consider exploring resources available on the Enterprise DNA Platform. This will greatly enhance your data modeling and analytical capabilities.