New to Guagues, Indicators and having trouble with the criteria

good morning,

i'm starting dip toes of of reports using guages, charts , indicators etc.

my first guage works , displays average dataset pointer shows:

=sum(fields!no_of_merges.value)/count(fields!no_of_merges.value)

what want add if possible either guage or pointer shows recent day against average (above).

i've attached sql below dataset based. here trick part. there no data weekend need display if report run on monday need display fridays no of merges else display previous days no of merges eg running today display yesterdays data.

----------------------------------------------------------  --by month  --------------------------------------------------------  select datepart(year,m.mergedttm) [year],   	   datepart(month,m.mergedttm) [month no],   	   datename(month,m.mergedttm) [month name],  	   datepart(week,m.mergedttm) [week],  	   datepart(day,m.mergedttm) [dayno],  	   datename(weekday,m.mergedttm) [day],  	     	   count(m.oid) [no of merges]    lzo_patientmerge m  m.status = 'a'    , m.operationmode = 'merge'  --  , datediff(day,m.mergedttm, getdate()) between 0 , 14  group datepart(year,m.mergedttm), datepart(month,m.mergedttm),            datename(month,m.mergedttm), datepart(week,m.mergedttm),  		 datepart(day,m.mergedttm) ,  		 datename(weekday,m.mergedttm)   order datepart(year,m.mergedttm)desc , datepart(month,m.mergedttm) desc,  	     datename(month,m.mergedttm) desc, datepart(week,m.mergedttm) desc,  	     datepart(day,m.mergedttm),   	     datename(weekday,m.mergedttm)

i have attached sample of first dashboard (early revision). have idea like.

hi simonkevans,

according description, want display data based on today’s date. example, today 2016/8/19 friday, , want make dataset query display yesterday’s (thursday) data. right?

to achieve requirement, can @ dataset query side specifying parameter dataset query. give parameter value today. can write clause based on parameter “yesterday”.

please refer following sample code:

declare @t table (dt date, val int) insert @t values ('2016-08-19',19),('2016-08-14',14),('2016-08-18',18),('2016-08-22',22)  select * @t   declare @dat date = dateadd(day,1 ,getdate()) select @dat  select * @t          (datepart(dw, dateadd(day,-1 ,@dat)) % 7 = 0 , dt= dateadd(day,-2 ,@dat)) -- @dat sunday        or        (datepart(dw, dateadd(day,-1 ,@dat)) % 7 = 1 , dt= dateadd(day,-3 ,@dat)) -- @dat monday        or        (datepart(dw, dateadd(day,-1 ,@dat)) % 7 > 1 , dt= dateadd(day,-1 ,@dat) ) --@dat not monday , sunday 

you can see code, if @dat = getdate() today friday, august 19, 2016 , data ‘2016-08-18’,18. if @dat= dateadd(day,3 ,getdate()), monday, august 22 , table return ‘2016-08-19’,19.

if still have questions, please feel free ask.
thanks, 
xi jin.

SQL Server  >  SQL Server Reporting Services, Power View