Filter KPI Value Relative to Selected Date

[WillDetzel]

Hey everyone!

I need some help with the following:
I have a table like this:

My Visual KPI:

So, what I want to do is, I want the target on the KPI to show the value on the column "Meta" on my auxiliar table (the first print), based on the selected date. And when there's no selected date, I want it to show the mos recent target (Meta).

However, I can't seem to be able to do this without creating a calendar table with the target, and that's not what I need. I need my measure to dinamically show me these target values based on the selected date between the "Data Inicial" and "Data Final". Please tell me if this is something that's not able to do this way as well, since I know I could do it with only measures, but I actually need to use the auxiliar table, in order to keep a track record of the KPIs.

What I already have is something like this (which is based on the same table, but with a date column, transforming it into a calendar table:

NPS Goal = 

VAR DataSelecionada = SELECTEDVALUE('Calendar'[Date])
VAR DataMaisRecente =
    CALCULATE(
        MAX(DM_KPIs[Data]),
        FILTER(
            DM_KPIs,
            DM_KPIs[KPI] = "NPS Brand"
        )
    )
RETURN
IF(
    ISBLANK(DataSelecionada),
    CALCULATE(
        MAX(DM_KPIs[Meta]),
        DM_KPIs[Data] = DataMaisRecente &&
        DM_KPIs[KPI] = "NPS Brand"
    ),
    CALCULATE(
        MAX(DM_KPIs[Meta]),
        FILTER(
            DM_KPIs,
            DM_KPIs[Data] = DataSelecionada &&
            DM_KPIs[KPI] = "NPS Brand"
        )
    )
)

EDIT: Forgot to add that I also need to filter the name, in this case being "NPS Brand".

Thanks in advance!

[v-zhengdxu-msft · Accepted solution]

Hi @WillDetzel 

In my opinion, it is necessary to create a calendar, and this calendar cannot have a relationship with your main table.

This allows you to select the corresponding date in the calendar and use Measure to determine it.

How to Get Your Question Answered Quickly

Best Regards

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

[v-zhengdxu-msft · Accepted solution]

Hi @WillDetzel 

In my opinion, it is necessary to create a calendar, and this calendar cannot have a relationship with your main table.

This allows you to select the corresponding date in the calendar and use Measure to determine it.

How to Get Your Question Answered Quickly

Best Regards

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

[WillDetzel · Accepted solution]

Thanks for tour reply!

I actually changed my approach, and talked to my client, we decided to not have such a historic table with the KPIs, and ended up just making a measure with an IF statement that changes the result based on the selected date.