How to calculate weighted weekday in DAX: step by step

João Barros 28 de September de 2026 5 min read

We will create a DAX measure that calculates a weighted average by weekday (for example, sales weighted by day importance). This is useful when some days have greater relevance, cost or impact on the business and you want to reflect that in analyses — for example, if Saturday has 50% higher traffic, or if Monday is less representative due to holidays. The idea is to apply weights by WeekdayNumber and calculate a weighted average that replaces the simple average or the simple total.

Prerequisites

Power BI Desktop with a simple model: FactSales table (Date, SalesAmount, Quantity) and Date table with WeekdayName and WeekdayNumber (1‑7). Note: by default DAX WEEKDAY returns 1=Sunday; adjust according to your calendar if you use 1=Monday).
Basic knowledge of DAX measures, relationships between tables and functions such as SUMX, VALUES and LOOKUPVALUE.
Goal: create a measure WeightedSalesByWeekday in DAX that reflects weights assigned to each weekday.

Step 1: Define the weekday weights table

We need a table that maps each weekday to a weight (for example, Mon=1, Sat=1.5). You can create a calculated table in DAX or load a table manually via Power Query/CSV. The advantage of DATATABLE is that it stays in the model and is easy to change. Example with plausible weights: weekdays 1.0, Thursday 1.2, Friday 1.3, Saturday 1.5.

WeekdayWeights =
DATATABLE(
  "WeekdayNumber", INTEGER,
  "Weight", DOUBLE,
  {
    {1, 1.0},  -- Domingo (ajusta conforme a tua WeekdayNumber)
    {2, 1.1},  -- Segunda
    {3, 1.0},  -- Terça
    {4, 1.0},  -- Quarta
    {5, 1.2},  -- Quinta
    {6, 1.3},  -- Sexta
    {7, 1.5}   -- Sábado
  }
)

Step 2: Ensure relationship between Date and WeekdayWeights

If your Date table has WeekdayNumber, create a relationship between Date[WeekdayNumber] and WeekdayWeights[WeekdayNumber]. This allows filtering by weekday and using RELATED or filter context rules. Without a relationship, you will need to use LOOKUPVALUE carefully, which can affect performance. Also confirm that the granularity of the Date table covers the entire FactSales period (for example, 3 years = ~1,095 rows).

Step 3: Basic sales measure

Create a simple measure to use as a base: SalesAmountTotal. It is the reference to compare the weighted result with the unweighted total.

SalesAmountTotal = SUM(FactSales[SalesAmount])

Step 4: Sale-level applied weight measure (optional)

If you have granularity in the fact table with the sale date, you can get the weight using RELATED through the Date → WeekdayWeights relationship (or via LOOKUPVALUE if there is no relationship). This allows you to see, row by row, which Weight was applied. Useful for debugging and validation: for example, if a sale on 2026‑08‑07 (Saturday) has weight 1.5.

SaleWeight = 
VAR wd = RELATED('Date'[WeekdayNumber])
RETURN
LOOKUPVALUE(WeekdayWeights[Weight], WeekdayWeights[WeekdayNumber], wd)

Step 5: Create the WeightedSalesByWeekday measure (recommended example)

The measure of interest is the sum of sales multiplied by the weight, divided by the sum of the weights (weighted average). We use CALCULATE and SUMX to iterate over the days in the Date table visible in the current context. This ensures that filters by period or by product are respected.

WeightedSalesByWeekday =
VAR VisibleDays = VALUES('Date'[Date])
VAR Numerator =
    SUMX(
        VisibleDays,
        CALCULATE(SUM(FactSales[SalesAmount])) *
        LOOKUPVALUE(WeekdayWeights[Weight], WeekdayWeights[WeekdayNumber], MAX('Date'[WeekdayNumber]))
    )
VAR Denominator =
    SUMX(
        VisibleDays,
        LOOKUPVALUE(WeekdayWeights[Weight], WeekdayWeights[WeekdayNumber], MAX('Date'[WeekdayNumber]))
    )
RETURN
IF(Denominator = 0, BLANK(), Numerator / Denominator)

Concrete example: if in a period you had 7 days with daily sales [100, 120, 80, 90, 110, 200, 150] and weights [1,1.1,1,1,1.2,1.3,1.5], the Numerator will be the sum of each sale*weight (~100*1 + 120*1.1 + ... = ~1,204) and the Denominator the sum of the weights (~8.1), resulting in a weighted average ~148.6 instead of the simple average ~120. A significant difference that shows the impact of weekend weight.

Step 6: Alternative version by aggregation by Weekday

A more efficient approach first aggregates by WeekdayNumber and then applies the weights. Useful for large volumes where iterating by each individual day is costly. Here you aggregate sales by Weekday and multiply by the weights, reducing LOOKUPVALUE calls.

WeightedSalesByWeekday_Agg =
VAR AggByWeekday =
    SUMMARIZE(
        'Date',
        'Date'[WeekdayNumber],
        "SalesPerDay", CALCULATE(SUM(FactSales[SalesAmount]))
    )
VAR Numerator =
    SUMX(
        AggByWeekday,
        [SalesPerDay] *
        LOOKUPVALUE(WeekdayWeights[Weight], WeekdayWeights[WeekdayNumber], 'Date'[WeekdayNumber])
    )
VAR Denominator =
    SUMX(
        AggByWeekday,
        LOOKUPVALUE(WeekdayWeights[Weight], WeekdayWeights[WeekdayNumber], 'Date'[WeekdayNumber])
    )
RETURN
IF(Denominator = 0, BLANK(), Numerator / Denominator)

Verify the result

Place the measures WeightedSalesByWeekday and SalesAmountTotal in a visual (table or card) and test with period and WeekdayName filters. Check known cases: if all weights are 1, the measure should equal SalesAmountTotal; if one day has a higher weight, that day should influence the result more. Also test with hour/store filters to ensure the Date granularity matches FactSales.

Conclusion

With these measures you can analyze sales (or any metric) adjusted by weekday weights — useful in operations, staffing or seasonal analysis. Next steps: parameterize the weights with a What‑if parameter to adjust interactively, or allow editing via Power Query. Final tip: avoid using LOOKUPVALUE inside very large loops unless necessary; the aggregated version tends to be more efficient in models with hundreds of thousands of records.