question

Amir-1460 avatar image
0 Votes"
Amir-1460 asked Amir-1460 answered

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

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









sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what is your expected results and why? You talk about WTD etc, but when you ask questions for help with a query, don't assume that people know your business domain.

0 Votes 0 ·

Could have any updates?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Amir-1460,

Welcome to the microsoft TSQL Q&A forum!
124497-image.png

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:
124551-image.png


 ;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:
124438-image.png


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.



image.png (26.1 KiB)
image.png (8.5 KiB)
image.png (9.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Amir-1460 avatar image
0 Votes"
Amir-1460 answered

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])


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.