KPI target goal measure takes last date from trend axis.

[Anonymous]

Hi,

I'm having an issue with the kpi visual in Power BI Desktop. I've created 2 measures. One to show me the sum of a value of the last date in the date table and one to show me the sum of the same value but of the first date. My target goal is the first date 'cause i want to measure if the sum is bigger or smaller than the one today. But the problem seems to be that the measure of the first date, takes the last date on the trend axis, so it's always the same value as the other measure. 

Can anybody help me? I've been looking for an answer for quite a while now..

Thanks in advance!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

I can reproduce your problem as below:

To reslove this, i create two measures

firstday_modified =
VAR firstday =
    CALCULATE ( FIRSTDATE ( Sheet2[date] ), ALL ( Sheet2[date] ) )
RETURN
    CALCULATE (
        SUM ( Sheet2[active users] ),
        FILTER ( ALL ( Sheet2 ), Sheet2[date] = firstday )
    )

lastday_modified =
VAR lastday =
    CALCULATE ( LASTDATE ( Sheet2[date] ), ALL ( Sheet2[date] ) )
RETURN
    CALCULATE (
        SUM ( Sheet2[active users] ),
        FILTER ( ALL ( Sheet2 ), Sheet2[date] = lastday )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, this worked for me! 

[v-juanli-msft]

Hi @Anonymous 

Could you show some example data?

Best Regards
Maggie

[Anonymous]

The problem does not lie with the data, i think it's the kpi visual that's the problem. You have to give a date field for the trend parameter, and i think it performs my measure on the last date. Here is my dax code;

Active users (last day) First date = CALCULATE(SUM('SSales internal report'[active users (last day)]);FIRSTDATE('SSales internal report'[export date]))

The problem i think is, that it takes the first date of the kpi trend date, but this is always the current date, so it always shows the same value as my other measure:

Active users (last day) last date = CALCULATE(SUM('SSales internal report'[active users (last day)]);LASTDATE('SSales internal report'[export date]))

Do you understand the problem? 

The trend value is: 'SSales internal report'[export date]