Calculating the % above/below the YTD average

[chillpill]

I have a table with a number of teams who produce a certain volume each week. What I am trying to do is create a measure that tells me the summed value for the selected weeks on a date slicer and what % above or below that summed value is compared to the YTD average. This is also divided by team.

For example, I have the week 1/13-1/17 selected on the slicer and the total volume it provides is 1095. What % above/below was 1095 compared to the total YTD average of the weeks prior? Would this be a running average? I can do this in excel fairly easily but having trouble formulating it in DAX.

Table: 

Team	Week Of	WeekNum	Volume
Team 1	01/06/2025	2	1004
Team 1	01/13/2025	3	1095
Team 1	01/20/2025	4	1120
Team 2	01/06/2025	2	389
Team 2	01/13/2025	3	312
Team 2	01/20/2025	4	350

Expected result (or similar):

Team	Week Of	WeekNum	Volume	% inc/dec from avg of prev weeks
Team 1	01/06/2025	2	1004	-
Team 1	01/13/2025	3	1095	4.3% above YTD average
Team 1	01/20/2025	4	1120	4.3% above YTD average
Team 2	01/06/2025	2	389	-
Team 2	01/13/2025	3	312	-11% below YTD average
Team 2	01/20/2025	4	350	-.08% below YTD average

[Anonymous]

Hi,

Thanks for the solution @DataNinja777  offered, and i want to offer some more information for user to refer to.

hello @chillpill , you can refer to the following solution.

Sample data 

Create a calendar table.(1:n with data table)

Calendar = CALENDAR(DATE(2024,1,1),TODAY())

 Then create a measure

MEASURE =
VAR a =
    CALCULATE (
        AVERAGE ( 'Table'[Volume] ),
        'Table'[Week Of] <= MAX ( [Week Of] ),
        'Table'[Week Of] >= DATE ( YEAR ( MAX ( [Week Of] ) ), 1, 1 ),
        ALLEXCEPT ( 'Table', 'Table'[Team] )
    )
VAR b =
    DIVIDE ( CALCULATE ( SUM ( 'Table'[Volume] ) ) - a, a )
VAR c =
    FORMAT ( b, "percent" )
RETURN
    SWITCH (
        TRUE (),
        b > 0, c & " above YTD average",
        b = 0, "-",
        b < 0, c & " below YTD average"
    )

Then create a table visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chillpill]

Thank you for this!

[DataNinja777 · Accepted solution]

Hi @chillpill ,

Using a properly configured calendar table for time intelligence calculations is a best practice in Power BI. A calendar table allows DAX functions to leverage built-in time intelligence features, making calculations more robust, reusable, and efficient.

To compute the percentage change compared to the YTD average using a calendar table, follow these steps. First, ensure your data model includes a dedicated calendar table with a continuous date range and relationships set up between the calendar table's date column and the fact table's date column. If you don't have a calendar table yet, you can create one using the following DAX formula:

Calendar =
ADDCOLUMNS(
    CALENDAR(MIN('Table'[Week Of]), MAX('Table'[Week Of])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "WeekNum", WEEKNUM([Date], 2),
    "WeekStart", [Date] - WEEKDAY([Date], 2) + 1
)

Once the calendar table is in place, create measures to calculate the required values. Start with the YTD total for the selected weeks:

YTD Volume =
CALCULATE(
    SUM('Table'[Volume]),
    DATESYTD('Calendar'[Date])
)

Next, calculate the YTD average by dividing the YTD volume by the number of weeks in the YTD period. Use the following measure:

YTD Average Volume =
DIVIDE(
    [YTD Volume],
    COUNTROWS(
        DATESYTD('Calendar'[Date])
    ),
    0
)

To calculate the percentage change from the YTD average, compare the current week's volume against the calculated YTD average. Use this DAX formula:

% Change from YTD Average =
VAR CurrentVolume = SUM('Table'[Volume])
VAR AvgVolume = [YTD Average Volume]
RETURN
IF(
    NOT ISBLANK(AvgVolume),
    DIVIDE(CurrentVolume - AvgVolume, AvgVolume, 0),
    BLANK()
)

For better readability, you can format the percentage change as follows:

% Change from YTD Average (Formatted) =
VAR Change = [% Change from YTD Average]
RETURN
IF(
    NOT ISBLANK(Change),
    FORMAT(Change, "0.0%") &
    IF(Change > 0, " above YTD average", " below YTD average"),
    "-"
)

By using a calendar table, the DAX measures leverage time intelligence functions like DATESYTD, which are optimized for date-based calculations. This approach is more scalable and ensures accurate results across various date ranges or filters. Moreover, a calendar table supports consistent time-based slicing and dicing, making your Power BI model more flexible and reliable.

Best regards,