Is there anyway to have the target of the guage to automatically change i.e. run rate?

[alexsimpson]

I have a target for the sales team to hit on a quarterly basis but I need to know where they should be at this time of quarter i.e. are they on target or not? i.e. the maximum on the guage can be set as a the target and then the 'target' on the guage will be how far through the quarter we are based on today's date as a percentage on the whole quarter multiplied by the total target. 

Is this possible to make the target a formula of this nature on the guage?

Many thanks in advance,

Alex

[Anonymous]

HI @alexsimpson,

If you want to create a dynamic target based on current date, you can take a look at below formula.

dynamic target = 
var todayAmount=LOOKUPVALUE('Sample Table'[Amount],[Date],TODAY())
var QuarterAmount=SUMX(FILTER(ALL('Sample Table'),[Date].[QuarterNo]=VALUE(FORMAT(TODAY(),"q"))&&[Date].[Year]=YEAR(TODAY())),[Amount])
var target=SUMX(FILTER(ALL('Sample Table'),[Date].[Year]=YEAR(TODAY())),[Amount])/4
return
todayAmount/QuarterAmount*target

Regards,

Xiaoxin Sheng

[alexsimpson]

Hi Xiaoxin,

Thanks so much for your reply. I have tried to use that formula but it is not registering anything? Do you know what I am doing wrong?

Many thanks in advance.

Alex

[Anonymous]

Hi @alexsimpson,

I found you have calculate on 'date' column instead of the amount, I think the issue may related to it.

If above not help, can you please provide some sample data to test?

Regards,

Xiaoxin Sheng

[alexsimpson]

Hi Xiaoxin,

Thanks for your reply. I have copied your formula exactly as shown below. So sorry about this, I am sure it is simple but it just wont work at the moment but you obviously managed to get it to work.

Thanks,

Alex