topic Re: Calculating the % above/below the YTD average in DAX Commands and Tips

Calculating the % above/below the YTD average

chillpill — Fri, 24 Jan 2025 20:28:36 GMT

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

Re: Calculating the % above/below the YTD average

DataNinja777 — Sat, 25 Jan 2025 01:15:29 GMT

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,

Re: Calculating the % above/below the YTD average

Anonymous — Mon, 27 Jan 2025 03:41:25 GMT

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.

Re: Calculating the % above/below the YTD average

chillpill — Thu, 30 Jan 2025 19:14:15 GMT

Thank you for this!