powerbidaxpowerbi-desktopmeasureweek-number

PowerBI DAX measure to restrict calculation to complete, 7-day weeks in current context

The end goal is to be able to calculate an AVERAGE weekly value for something while only considering days which are part of weeks which are fully included in the current filter context. As an incremental step, the dynamic determination of days (and their associated, related values) into complete-week-day and incomplete-week-days would also be helpful.

So, as an example, say we have a Calendar table joined to a standard Sales table. The Sales table is very simple, just a column for the date, a column for the item, and a column for the number sold:

Day Item Amount
2023-04-01 A 5
2023-04-01 B 5
2023-04-02 A 5
2023-04-03 A 5
2023-04-04 A 2
2023-04-05 A 2
2023-04-07 A 2
2023-04-08 A 2

And so on. Imagine the data continuing until the end of the month - that is the filter context (say, we're filtering on the Date in the Calendar table). In this specific example, assuming ISO standard (weeks start on Monday), 2023-04-01 and 2023-04-02 are not part of a complete week, but the rest of April comprise 4 full, entire 7-day weeks.

As you can see, there are 2 sales every day starting from the 3rd, so the average per week is 2x7=14. A more naive, incorrect calculation could for example just sum the entire month (28x2 + 2x5), divide by the number of weeks (30/7=4.2857...), and arrive at a value of 15.4. This is not the calculation I would like to go with, for reasons I won't get into here. I want to discard the first 2 days of April (and any other truncated weeks), sum the Amounts, and divide by the number of complete weeks.

If the filter context included March as well, April 1st and 2nd would be part of the previous week (the 13th week of 2023), and thus they should be counted. In that case, the first 5 days of March would be discarded.

The really simple way to solve this would be if calculated columns in PBI were dynamic, i.e. they responded to filters, which would mean a simple dynamic, IsFullWeek column in the Calendar table could be used as a filter. Unfortunately, that's not how DAX works, so another way is needed.

I've gotten as far as the following:

AVERAGEX(KEEPFILTERS(VALUES('Calendar'[IsoWeekOfYear])), CALCULATE(SUM('Sales'[Amount])))

This gets the average per week alright, but I can't figure out how to filter the incomplete weeks, and to be honest, I'm not entirely sure how it works w.r.t. the KEEPFILTERS. I tried this tp filter, but no luck, and I don't understand why.

AVERAGEX(KEEPFILTERS(VALUES('Calendar'[IsoWeekOfYear])), CALCULATE(SUM('Sales'[Amount]), COUNT('Calendar'[IsoWeekOfYear]) = 7)))
Solution

  • Here you go.

    Sales:

    enter image description here

    Slicer:

    enter image description here

    Output:

    enter image description here

    Measure:

        Average Sales (Whole Weeks) = 
    VAR m = CALCULATE(MIN(Sales[Day]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2) = 1))
    VAR s = CALCULATE(MAX(Sales[Day]), KEEPFILTERS(WEEKDAY('Calendar'[Date],2) = 7))
    
RETURN 
AVERAGEX( DATESBETWEEN( 'Calendar'[Date], m,  s), CALCULATE(SUM( Sales[Amount]) ) )

    Measure gets the first Monday in the current context and the last Sunday in the current context. It then uses those two dates to filter the AVERAGEX calculation.