1

So I created a little tablix with a row group for the YEAR_QTR (for example 2021_Q1 we are in today). It shows daily details, then some quarterly averages below. A simple image below of what I'm talking about. I have this all working but what I would like to add is another row underneath for the YTD totals per quarter

enter image description here

So ideally the output would look something like this:

YEAR_QTR  |   BUSINESS DATE  |  CREDITS  |
2020_Q1        1/15/2020           8
               2/1/2020            2
               3/16/2020           5
               3/22/2020           10
          |  QUARTERLY AVG   |    6.25   |  -- just Q1 2020
          |  YTD AVG         |    6.25   |  -- just Q1 2020

2020_Q2        4/15/2020           15
               5/1/2020            3
               5/11/2020           1
          |  QUARTERLY AVG   |    6.33   |  -- just Q2 2020
          |  YTD AVG         |    6.29   |  -- AVG for both Q1 & Q2 2020 

etc....

Is there a way I can create a calculation in that YTD TOTAL bucket, that can allow me to average the data from Jan 1 of that quarters year until that specific quarter? Or will the grouping only have access to the data from that quarter? I was almost thinking something like:

=IIF( BUSINESS DATE BETWEEN 1/1/YEAR AND MAX(of current qtr) then SUM(CREDITS)

Would something like that work or would the data from the other quarters from each year be out of the scope for that grouping?

Any guidance would be greatly appreciated!!

Jay
  • 455
  • 3
  • 17
  • 34
  • For this, I would look into using the RunningValue or a way to get the running sum of credits / Running Count of rows... which would output the rolling average. Here is a helpful article: https://www.mssqltips.com/sqlservertip/2587/cumulative-aggregates-in-sql-server-reporting-services/ – Jesse Jan 14 '21 at 22:57

0 Answers0