Dynamic Measure for Card Visual

[brainiacsloth_]

Hi.
I am working on a card visual.
The card is supposed to do two three things:

1. Show the current Red Score (an average of a column called KPI), which refers to a column called kpi_name, which has Red Score & Sparkling Availability; Red Score is what we are interested in. 

2. It should show the previous Red Score and it should be able to be responsive for both month and year filters.

3. It should have another Growth measure which shows the percentage growth or decline of from the previous year/month (levels of the Date slicer).

My problem is all the measures I have created aren't working. The first approach I used did not show the correct previous red score. This is the code:

Previous RED KPI =
CALCULATE(
AVERAGE(Traxx[KPI]),
FILTER(
ALL(Traxx), -- this makes all columns available
Traxx[session_date] = EOMONTH(MAX(Traxx[session_date]), -1)
&& Traxx[kpi_name] = "RED SCORE"
)
)

For February 2025, it showed my previous Red Score to be 81.84, even though it was 80.15, the growth% was also off. 

Then I tried a different formula:

Previous RED KPI =
VAR CurrentMonth = MONTH(MAX(Traxx[session_date]))
VAR CurrentYear = YEAR(MAX(Traxx[session_date]))
VAR PrevMonthDate =
EOMONTH(DATE(CurrentYear, CurrentMonth, 1), -1)

RETURN
CALCULATE(
AVERAGE(Traxx[KPI]),
Traxx[kpi_name] = "Red Score",
MONTH(Traxx[session_date]) = MONTH(PrevMonthDate),
YEAR(Traxx[session_date]) = YEAR(PrevMonthDate)
)

But there was no responsiveness from the growth or previous red score measures at a month level, it was blank and at an annual/year slicer level, it was still inaccurate. E.g. Red score for 2024 is 80.16, but when I select 2025, it shows previous red score to be 81.09%.

Please assist. 

Also, the data has multiple entries per date:

[Elena_Kalina · Accepted solution]

Hi @brainiacsloth_ 

1 Create DimDate

Dim_Date = 
VAR MinDate = MIN(Traxx[session_date])
VAR MaxDate = MAX(Traxx[session_date])
RETURN
    ADDCOLUMNS(
        CALENDAR(MinDate, MaxDate),
        "Day", DAY([Date]),
        "Month", MONTH([Date]),
        "MonthName", FORMAT([Date], "MMMM"),
        "Quarter", "Q" & QUARTER([Date]),
        "Year", YEAR([Date]),
        "YearMonth", FORMAT([Date], "yyyy-MM"),
        "MonthYearShort", FORMAT([Date], "MMM-yy"),
        "IsWeekend", WEEKDAY([Date], 2) > 5
    )

2 Create measures

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

Previous RED KPI = 
VAR CurrentDate = MAX(Dim_Date[Date])  // Gets the current date from context
VAR PreviousMonthStart = DATE(YEAR(CurrentDate), MONTH(CurrentDate) - 1, 1)  // Start of previous month
VAR PreviousMonthEnd = EOMONTH(PreviousMonthStart, 0)  // End of previous month

RETURN
    CALCULATE(
        AVERAGE(Traxx[KPI]),
        Traxx[kpi_name] = "Red Score",
        Dim_Date[Date] >= PreviousMonthStart,
        Dim_Date[Date] <= PreviousMonthEnd,
        REMOVEFILTERS(Dim_Date[MonthYearShort])  // Clears the current month filter
    )

Growth % = 
DIVIDE(
    [Current RED Score] - [Previous RED KPI],
    [Previous RED KPI],
    0  // Returns 0 in case of division by zero
)

                           

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[DataNinja777]

Hi @brainiacsloth_ ,

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,

[speedramps]

I want to help you but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.