KPI Card Target (MaxMonthlyExports) Not Showing Correct Value When Using Year 2024 Slicer

[dheerajyss]

Hi everyone,

I’m encountering an issue with my KPI card visual where the target value (calculated by my MaxMonthlyExports measure) doesn’t show the correct maximum monthly exports for the selected year in slicer.

My Question

How can I modify my MaxMonthlyExports measure so that it correctly returns the maximum monthly exports for 2024?

Please refer to the below image, where I have hilighted the issue which is the Goal in yellow for the KPI card visual with title LatestMonthExports and MaxMonthlyExports by Year Month Number.

What I’m Trying to Achieve

I have a Power BI report that uses a slicer based on a date hierarchy (specifically, tDate[Year][Month]). When I apply the slicer for the year 2024, my KPI card visual is supposed to display the following:

• Value: The latest monthly exports using my LatestMonthExports measure.

• Trend Axis: The month ordering provided by tDate[Year Month Number].

• Target: The maximum monthly exports for the selected year (calculated by MaxMonthlyExports).

For example, under the 2024 slicer:

• Expected LatestMonthExports: 2599

• Expected Target (MaxMonthlyExports): 3074 (from October 2024)

Instead, both the value and the target show 2599 (with the target label showing 2024-12), meaning the KPI card target is returning the same value as the latest month’s exports, rather than the maximum across all months.

The tables responsible:

tExports and tDate(calculated table)

Table Relationship:

Data: 

tExports:

tDate

Measures Involved

Here are the DAX measures I’m using:

• LatestMonthExports

LatestMonthExports = 
VAR LatestDate = LASTDATE(tExports[weekEndingDate])
VAR LatestEOM = EOMONTH(LatestDate, 0)
VAR Result = 
    CALCULATE(
        SUM(tExports[weeklyExports]),
        EOMONTH(tExports[weekEndingDate], 0) = LatestEOM
    )
RETURN
    Result

• MonthlyExports

MonthlyExports = 
VAR Result = 
    CALCULATE(
        SUM(tExports[weeklyExports])
    )
RETURN
    Result

• MaxMonthlyExports

MaxMonthlyExports = 
VAR Result = 
    MAXX(
        VALUES(tDate[Month Year]),
        [MonthlyExports]
    )
RETURN
    Result

• MaxMonthToolTip

MaxMonthToolTip = 
VAR MaxMonth =
    SELECTCOLUMNS(
        TOPN(1, VALUES(tDate[Month Year]), [MaxMonthlyExports], DESC),
        "MaxMonth", tDate[Month Year]
    )
RETURN
    MaxMonth

 

How to Reproduce

1. Apply the Year slicer for 2024.

2. Use the KPI card visual with:

   • Value field: LatestMonthExports

   • Trend Axis: tDate[Year Month Number]

   • Target field: MaxMonthlyExports

3. Observe that the KPI card shows 2599 for both the value and the target, with the target’s label showing 2024-12, even though the correct maximum should be 3074 (occurring in October 2024).

I have attached the sample Excel data source and the Power BI (.pbix) file for reference. [Link](USDA FAS ESR)

Any help or suggestions would be greatly appreciated! 

Thanks in advance for your assistance.

[dheerajyss · Accepted solution]

Hi @Anonymous,

Thank you for your time.

I have fixed the issue where the KPI cad does not respect the Year slicer passed on.

I fixed it by changing the logic for .MaxMonthlyExports measure, I used ALLSELECTED(tDate) which will respect the filters coming from outside while ignoring the filters appled inside query ( Source: ALLSELECTED – DAX Guide )

MaxMonthlyExports = 
VAR MaxExportValue = 
    IF(
        NOT ISBLANK([MonthlyExports]),
        CALCULATE(
            MAXX(
                VALUES(tDate[Month Year]),
                [MonthlyExports]
            ),
            ALLSELECTED(tDate)
        ),
        BLANK()
    )
RETURN
    MaxExportValue

Results for KPI card:

1. When no slicer applied:

The latest Month Exports are 674 for March 2025 at Value field. ✅

The Max Exports Month are 4350 for February 2025 at Goal field aka Taget field. ✅

2. When slicer year 2024 applied:

The latest Month Exports are 2599 for December 2024 at Value field. ✅

The Max Exports Month are 3074 for October 2024 at Goal field aka Taget field. ✅

As I found the issues resolved, I am myself marking this reply as the solution.

Please let me know if there are questions or any issues.

Thank you.

[Anonymous]

Hi @dheerajyss, 

We suggest you change the Trend axis ato Date_Year, as shown below:

Best regards,

Joyce

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

[dheerajyss]

Hi @Anonymous ,

Thank you for responding.

My observations from your suggested solution:

• I noticed that including only the Year from the Date hierarchy resolves the issue with the KPI card and now displays the correct Max exports found for the 2024 year slicer,
• Also it displays only the year in the KPI card as we included only the year,
• However, I noticed it causes the background trend to disappear in the KPI card up on including only the Year in trend axis field.

What I am expecting:

• Ideally, I would like both the Year and Month to appear within the KPI card (for this we need to include the Year Month Number from tDate), while retaining the background trend and also to get the desired Max export value for the given slicer 2024.

If there is no alternative way to achieve this, I might have to let go of this requirement for now.

My Questions:

• I am curious as to why using both Month and Year in the trend axis seems to affect the Goal Max Export data value in the KPI card for slicer year 2024. Could you clarify this?
• Is the Max export measure correct?

Thank you again for your time and help!