how to make KPI indicator an aggregated number rather than the most recent month?

[Anonymous]

Hi, 

when i tried to use slicer to specify the date range and KPI to show the number change. it always shows the most recent month number rather than sum of them. (whereas when i put these two measures into a card, it is correct)

i use this measure for my indicator: 

CALCULATE(COUNTROWS('leads final'), datesbetween('leads final'[modifiedon (bins)], FIRSTDATE('leads final'[modifiedon (bins)]), LASTDATE('leads final'[modifiedon (bins)])))

this one for my target (which is same period from the last year, like indicator: Jan-Mar, 2019 and target: Jan-Mar, 2018): 

target = CALCULATE(COUNTROWS('leads final'), datesbetween('leads final'[modifiedon (bins)], FIRSTDATE(DATEADD('leads final'[modifiedon (bins)], -12, MONTH)),LASTDATE(DATEADD('leads final'[modifiedon (bins)], -12, MONTH))))

more info: "Modifiedon (bins)" is the slicer.  

thank you so much 

[v-diye-msft]

Hi @Anonymous ,

Please correct me if I go wrong:

The point is you'd like to show sum of the filtered date range, rather than the value of lastest date. 

you can use below formula: 

measure = CALCULATE(SUM('leads final'[Value]),ALLSELECTED('leads final'[modifiedon (bins)]))

please share me more details about your data if further help needed. it's prefered to send me the your dummy pbix file with the expected result.

Best regards,

Dina Ye

[Anonymous]

basically, i want the goal to show as the same period from last year. so for example, i choose Feb and Mar 2019. the indicator is correct but goal is not from the last year. it is the same. 

i created these two measures. pls see the formula from my previous post 

thank you

[v-diye-msft]

Hi @Anonymous ,

Please let me know if you'd like to get the result like this:

Pbix attached: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Eflqd0gqXrBGmvlytj76pV4Beot2yACM5OSFHcaVc3B1cQ?e=BCicHh

[Anonymous]

hi @v-diye-msft 

this is almost what i want. but when i select two or more months in your slicer, it broke. it's very close. if you have some more time, pls feel free to continue. 

thank you so much 

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

I've fixed the measure, please check this one.

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Eflqd0gqXrBGmvlytj76pV4Beot2yACM5OSFHcaVc3B1cQ?e=DoQLwA

[Anonymous]

Hi, can you write the fixed measure here ?

[Anonymous]

@v-diye-msft hi can you pls take a look at this new question? 

@v-diye-msft @v-diye-msft @v-diye-msft @v-diye-msft 

it is very close!

thanks

[v-diye-msft]

Hi @Anonymous ,

It works well in my side, would you mind sharing me your dummy pbix?

[Anonymous]

yeah, would you mind give me your email address? the file sharing system is so irratating here. thanks!

[Anonymous]

@v-diye-msft 

Hi, i have a new question for you regarding this problem. 

when i set the visual level filter, the target goal did not pass this filter. even indicator changed based on this filter, target did not change. see example below: i set cdi_country is U.S. and there is another visual that i set not equal to U.S. 

 

[Anonymous]

this is really IMPRESSIVE! thank you for your help!