Dynamic Measure for Card Visual

Hi Anonymous ,

Your approach to solving this DAX problem is quite logical, but it encounters a common hurdle related to how DAX handles dates and filter contexts. The issue with your formulas stems from manually calculating previous periods using functions like EOMONTH and MAX. This method is often not robust enough because it struggles to adapt when the user changes the filter context, such as switching from a monthly view to an annual view. The standard and most reliable solution in Power BI is to implement a dedicated Date Table. This table acts as a definitive source for all date-related filtering and allows you to use powerful, built-in time intelligence functions.

The first step is to create this essential Date Table. In the Data view of Power BI, you can create a new table using the following DAX expression. This script will generate a table named 'Date' with a continuous range of dates based on your data, along with helpful columns for Year, Month, and more.

Date =
ADDCOLUMNS (
    CALENDAR ( MIN ( Traxx[session_date] ), MAX ( Traxx[session_date] ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Year Month", FORMAT ( [Date], "yyyy-mm" )
)

Once the Date table is created, you must establish a relationship between it and your Traxx data table. Navigate to the Model view and connect them by dragging the Date column from your new Date table onto the session_date column in the Traxx table. This one-to-many relationship is critical as it allows filters applied to the Date table to correctly propagate to your main data. From this point forward, it is vital that any date slicers in your report use the columns from this new Date table, not the original session_date column.

With the model properly set up, you can now create the necessary measures. First, a base measure for the current Red Score simplifies subsequent formulas. This measure calculates the average KPI for rows where the kpi_name is "Red Score" within the current filter context.

Red Score =
CALCULATE(
    AVERAGE(Traxx[KPI]),
    Traxx[kpi_name] = "Red Score"
)

Next, you can write the dynamic measure to find the previous period's score. This formula checks the current filter context to determine whether the user is viewing data by month or by year, and then calculates the score for the corresponding previous period using the DATEADD function. It is far more robust than manually calculating dates.

Previous RED KPI =
VAR IsMonthFiltered = ISFILTERED('Date'[Month]) || ISFILTERED('Date'[Year Month])
VAR IsYearFiltered = ISFILTERED('Date'[Year])

RETURN
SWITCH(
    TRUE(),
    IsMonthFiltered,
    CALCULATE(
        [Red Score],
        DATEADD('Date'[Date], -1, MONTH)
    ),
    IsYearFiltered,
    CALCULATE(
        [Red Score],
        DATEADD('Date'[Date], -1, YEAR)
    )
)

Finally, the growth percentage measure becomes a simple calculation that leverages the two measures you have already created. Using the DIVIDE function is a best practice as it gracefully handles any situations where the Previous RED KPI might be blank or zero, thus preventing errors in your visual.

Growth % =
DIVIDE(
    [Red Score] - [Previous RED KPI],
    [Previous RED KPI]
)

After creating this final measure, remember to select it and use the Measure tools to format it as a percentage. You can then assemble these three measures in your card visual to display the current score, the previous score, and the growth percentage, all of which will now respond correctly to your year and month slicers.

Best regards,