Calculating the % above/below the YTD average

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,