Delta Calculation strange results

[Anonymous]

Hi there,

I have two tables in my SQL Server 2017 The CurrentMemberYr produce the expected results but

The CurrentMemberBr with the slicer did not as you can see in the attachment

How do I fix this? Do I need to put middle table like many to many?

Here is the code in TSQL for PowerBI (two tables)

create table CurrentMemberYr
(
Years int null,
CurrentMembers int null
)
GO
Insert into CurrentMemberYr(Years,CurrentMembers)
Values(2015,200),(2016,300),(2017,550),(2018,400)

--CurrentMemberYr in PowerBI
With myCTE
AS
(
Select Years,
CurrentMembers
from CurrentMemberYr
)
Select Years,
CurrentMembers,
Delta = CurrentMembers - Lag(CurrentMembers) Over (order by Years)
From myCTE

create table CurrentMemberBr
(
Years int null,
Branch Varchar(10) null,
CurrentMembers int null
)
Insert into CurrentMemberBr(Years,Branch,CurrentMembers) Values
(2015,'A',200),(2016,'A',300),(2017,'A',550),(2018,'A',400),
(2015,'B',200),(2016,'B',300),(2017,'B',550),(2018,'B',400),Current
(2015,'C',200),(2016,'C',300),(2017,'C',550),(2018,'C',400),
(2015,'D',200),(2016,'D',300),(2017,'D',550),(2018,'D',400)

--CurrentMemberBr in PowerBI
;With myCTE
AS
(
Select Years,
Branch,
CurrentMembers
from CurrentMemberBr
)
Select Years,
Branch,
CurrentMembers,
Delta = CurrentMembers - Lag(CurrentMembers) Over (order by Years)
From myCTE

 

[DanielV91 · Accepted solution]

Hello @Anonymous

Can you please try the following query and let me know if it works?

SELECT 
  Branch,  
  Years,  
  CurrentMembers,
  CASE 
    WHEN Branch != LAG(Branch, 1) OVER (ORDER BY Branch, Years) THEN NULL 
    ELSE CurrentMembers - LAG(CurrentMembers,1) OVER (ORDER BY Branch, Years) 
  END AS Delta
FROM CurrentMemberBr
ORDER BY Branch, Years

[Anonymous]

Daniel,

Yes it works

Thank you so much

Oded Dror

[quentin_vigne]

Hi @Anonymous

Did you add a relationship between your two tables in the relationship tab ? 

If not you should probably add a new one on an PK value.

Also check if it is a bidirectionnal filter between them 

- Quentin

[Anonymous]

Quentin,

No, there is no connection between them. Table CurrentMemberYr is to show what the currect value looks like (expected results)

Table CurrentMemberBr show the error, I think I need to add dynamic parameter and convert the code to a stored proc

Thanks,

Ed Dror