Currently I am trying to do a proof of concept for MTD sales and YTD sales and am using the ssrs expression RunningValue and I can get the YTD correct by month but can not get the total to work with the quarter column group in my matrix. It always ends up being the previous months total and not all months totaled like it is in the MTD column.
This picture is an example of what I get with the MTD totals and YTD being correct until it gets to the Q1 value
This is what the Q1 total should equal out to.
ColumnGroup3 is the month column that I am showing, and I've tried to set the running value scope to QT,DataSet1(name of dataset), and nothing.
=RunningValue(Fields!ExtendedPrice.Value, Sum, "QT")
Along with that I have tried to hard code it but it always ends up being Septembers total with anything I try.
DECLARE @Fiscal INT = '2024';
WITH Fiscal AS (
SELECT MIN(StartDate) OVER (PARTITION BY FiscalYear) AS START, MAX(EndDate) OVER (PARTITION
BY FiscalYear) AS ENDa, AccountingPeriodID, StartDate, DATEPART(month,StartDate) AS MONTH, DATEPART(year, StartDate) AS YEAR
FROM bi.GLFiscalYearComposite
GROUP BY StartDate, FiscalYear, EndDate, AccountingPeriodID
HAVING (FiscalYear = @Fiscal)),QUARTER AS(SELECT
CASE WHEN MONTH BETWEEN 7 AND 9 THEN 'Q1'
WHEN MONTH BETWEEN 10 AND 12 THEN 'Q2'
WHEN MONTH BETWEEN 1 AND 3 THEN 'Q3'
WHEN MONTH BETWEEN 4 AND 6 THEN 'Q4'
ELSE NULL END AS QT,
CASE WHEN MONTH BETWEEN 7 AND 9 THEN 21000
WHEN MONTH BETWEEN 10 AND 12 THEN 73500
WHEN MONTH BETWEEN 1 AND 3 THEN 381500
WHEN MONTH BETWEEN 4 AND 6 THEN 357000
ELSE NULL END AS YTDQTGOAL
,GLBATCH.AccountingPeriodID
FROM Fiscal INNER JOIN
GLBATCH ON Fiscal.AccountingPeriodID = GLBATCH.AccountingPeriodID INNER JOIN
CUSORDER ON GLBATCH.GLBatchID = CUSORDER.OrderBatchID)
SELECT QT,MONTH,SUM(CUSORDER.ExtendedPrice) OVER(PARTITION BY MONTH ORDER BY QT) AS RUNNING_TOTAL
FROM GLBATCH INNER JOIN
Fiscal ON GLBATCH.ACcountingPeriodID = Fiscal.AccountingPeriodID INNER JOIN
QUARTER ON GLBATCH.ACcountingPeriodID = QUARTER.AccountingPeriodID INNER JOIN
CUSORDER ON GLBATCH.GLBatchID = CUSORDER.OrderBatchID
GROUP BY ExtendedPrice,QT,START,todate,YEAR,SNUM,MONTH
HAVING CUSORDER.date > START
ORDER BY QT
So I've tried
SUM(ExtendedPrice) OVER(PARTITION BY MONTH ORDER BY QT) AS RUNNING_TOTAL
AND
SUM(ExtendedPrice) OVER (partition BY YEAR,MONTH
ORDER BY QT ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL
If anyone has any idea of a way to do this within the matrix or a better way to run this code I would appreciate any help!


