Replicating DAX's CALCULATE function in SQL for WTD, MTD, QTD and YTD calculations

Question

Hello,

I have built a Power Pivot Excel report to calculate WTD, MTD, QTD, YTD calculations. The problem is that Excel is far too big (80 MB) so I need to do the calculations in SQL and just have Excel making the report look pretty.

The key points are:

• The problem domain is banking accounts
• The grain of the data is daily snapshots i.e. end of day closing balances for each account
• The datasets contains data for Today, Last Week (defined as Sunday), Last Month, Last Quarter and Last Year
• I need to calculate Values Movements (e.g. Balance Today - Balance Last Year)
• I need to calculate Volume Movements (e.g. Number of account Today - Number of accounts Last Year) by Segment

Below is a sample data set and the two reports that I need to produce. I have seen various ways of doing this, including Windows Functions. Is there a recommended and simple way of doing this in T-SQL?

---------------------------------------------------------------------------------------
-- Sample data
---------------------------------------------------------------------------------------

drop table if exists #Data

create table #Data
(
    SnapshotDate date not null
,   AccountNumber int not null
,   Segment varchar(20) not null
,   Balance money not null
)

-- Account 123 : existed in every period
insert into #Data values('18Aug2021', 123, 'Accountancy', 45)
insert into #Data values('15Aug2021', 123, 'Accountancy', 55) -- Last Sunday
insert into #Data values('31Jul2021', 123, 'Accountancy', 1506) -- Last Month   
insert into #Data values('30Jun2021', 123, 'Accountancy', 4560) -- Last Quarter
insert into #Data values('31Dec2020', 123, 'Accountancy', 20) -- Last year

-- Account 456 : did not exist end of last year
insert into #Data values('18Aug2021', 456, 'Accountancy', 345)
insert into #Data values('15Aug2021', 456, 'Accountancy', 10) -- Last Sunday
insert into #Data values('31Jul2021', 456, 'Accountancy', 550) -- Last Month    
insert into #Data values('30Jun2021', 456, 'Accountancy', 450) -- Last Quarter

-- Account 789 : missing latest snapshot
insert into #Data values('15Aug2021', 789, 'Pension', 1) -- Last Sunday
insert into #Data values('31Jul2021', 789, 'Pension', 220) -- Last Month    
insert into #Data values('30Jun2021', 789, 'Pension', 434) -- Last Quarter
insert into #Data values('31Dec2020', 789, 'Pension', 800) -- Last year


select * 
from #Data
order by SnapshotDate asc, AccountNumber


---------------------------------------------------------------------------------------
-- All Accounts Report
---------------------------------------------------------------------------------------


drop table if exists #AllAccountsReport

create table #AllAccountsReport
(
    AccountNumber int not null
,   Segment varchar(20) not null
,   BalanceToday money not null
    -- Value Movements
,   WTD_Value money not null
,   MTD_Value money not null
,   QTD_Value money not null
,   YTD_Value money not null
)

select *
from #AllAccountsReport


---------------------------------------------------------------------------------------
-- Segment Report
---------------------------------------------------------------------------------------


drop table if exists #SegmentReport

create table #SegmentReport
(
    Segment varchar(20) not null
,   BalanceToday money not null
    -- Value Movements
,   WTD_Value money not null
,   MTD_Value money not null
,   QTD_Value money not null
,   YTD_Value money not null
    -- Volume Movements
,   WTD_Volume money not null
,   MTD_Volume money not null
,   QTD_Volume money not null
,   YTD_Volume money not null
)

select *
from #SegmentReport

Answer 1

Hi @Amir ,

Welcome to the microsoft TSQL Q&A forum!

Sorry, your question is not clear enough for me, you mentioned Balance Today-Balance Last Year, but today’s balance has two data according to different AccountNumber: 45 and 345, which one should we choose?

Similarly, you mentioned calculate Volume Movements (e.g. Number of account Today-Number of accounts Last Year) by Segment. But some segments under the same date have different AccountNumbers, such as
The AccountNumber of 2021-08-18 contains 123 and 456. Which one should we choose?

The following is a method based on my guess, please check if it works for you:

;WITH cte  
as(SELECT * FROM (SELECT SnapshotDate,AccountNumber,Segment,Balance   
 FROM  #Data) t  
 PIVOT (MAX(Balance) FOR SnapshotDate   
 IN([2021-08-18],[2021-08-15],[2021-07-31],[2021-06-30],[2020-12-31])) p)  
  
SELECT AccountNumber,Segment,[2021-08-18] BalanceToday,  
[2021-08-18]-[2021-08-15] AS WTD_Value,  
[2021-08-18]-[2021-07-31] AS MTD_Value,  
[2021-08-18]-[2021-06-30] AS QTD_Value,  
[2021-08-18]-[2020-12-31] AS YTD_Value  
FROM cte   

Output:

;WITH cte  
as(SELECT SnapshotDate,Segment,SUM(AccountNumber) AccountNumber  
FROM #Data  
GROUP BY SnapshotDate,Segment)  
,cte2 as(SELECT * FROM (SELECT SnapshotDate,AccountNumber,Segment  
 FROM  cte) c  
 PIVOT (MAX(AccountNumber) FOR SnapshotDate   
 IN([2021-08-18],[2021-08-15],[2021-07-31],[2021-06-30],[2020-12-31])) p)  
,cte3 as(SELECT * FROM (SELECT SnapshotDate,AccountNumber,Segment,Balance   
 FROM  #Data) t  
 PIVOT (MAX(Balance) FOR SnapshotDate   
 IN([2021-08-18],[2021-08-15],[2021-07-31],[2021-06-30],[2020-12-31])) p)  
  
SELECT c3.Segment,c3.[2021-08-18] BalanceToday,  
c3.[2021-08-18]-c3.[2021-08-15] AS WTD_Value,  
c3.[2021-08-18]-c3.[2021-07-31] AS MTD_Value,  
c3.[2021-08-18]-c3.[2021-06-30] AS QTD_Value,  
c3.[2021-08-18]-c3.[2020-12-31] AS YTD_Value,  
c2.[2021-08-18]-c2.[2021-08-15] AS WTD_Volume,  
c2.[2021-08-18]-c2.[2021-07-31] AS MTD_Volume,  
c2.[2021-08-18]-c2.[2021-06-30] AS QTD_Volume,  
c2.[2021-08-18]-c2.[2020-12-31] AS YTD_Volume  
FROM cte2 c2  
JOIN cte3 c3 ON c2.Segment=c3.Segment  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you EchoLiu-msft - that's very helpful and whilst it works, it confirmed my suspicion, for my scenario, DAX is better suited for this sort of thing.

In case anyone is interested, using Excel + DataModel + DAX, I have managed to provide all of the required calculation using, e.g.:

YTD_Value:=CALCULATE([TotalValue], 'pqFactsByPeriod'[DatePeriod] = "P0") - CALCULATE ([TotalValue], 'pqFactsByPeriod'[DatePeriod] = "P5")

YTD_Volume:=CALCULATE([TotalVolume], 'pqFactsByPeriod'[DatePeriod] = "P0") - CALCULATE ([TotalVolume], 'pqFactsByPeriod'[DatePeriod] = "P5")

Where:
"P0" is the flag on records for the latest facts (i.e. today)
"P5" is the flag for facts for 31 Dec 2020
TotalValue:=sum([Value])
TotalVolume:=count([Value])