KPI Card displays last GOAL row instead of VALUE row

[bmjacques]

Hello,

I am trying to create a KPI card that shows:

VALUE: Total Members

TREND AXIS: Start of Month

TARGET: Goal Total Members

I want this to update monthly as I add new data, but to display the most recent month that has data in the "Total Members" measure. I believe my problem is that when I add the "Goal Total Members" to the "TARGET" field on my KPI card, it defaults to the last row, which is January 2025 instead of January 2024.

The formula for the measures are:

Previous Year Total Members = CALCULATE([Total Members],

    DATEADD('Date Lookup'[Date],-1,YEAR))

Goal Total Members = [Previous Year Total Members] *1.05

Is there something I can add to the measures to only calculate if the "Total Members" value is not blank?

[Anonymous]

Hi @bmjacques 

Not sure what's your data like, so I create a demo with some dummy columns that may be different from yours. You can modify the measure according to your data. 

Current Goal = 
var curMonth = CALCULATE(MAX('Table'[Start of Month]), NOT(ISBLANK('Table'[Members])))
return
CALCULATE([Total Members],'Table'[Start of Month]=EDATE(curMonth,-12))*1.05

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[bmjacques]

Hi Jing,

Thank you for your reply. I'm having some trouble with the formula, I think because the table in the screenshot I sent was made from measures and not the raw data from the model. I made some tweaks to the formula you sent to match my fields, but I think the "NOT(ISBLANK([Total members])))" is where I have an issue because "Total Members" is a measure, not a column.

I shared a link below to the OneDrive file if seeing the raw data is helpful.

Renewal Billing Data = fact table

Date Lookup = date table

Measure Table = holds all measures

Membership Statistics to Share