Forum Discussion
Delta Calculation strange results
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
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
4 Replies
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
quentin_vigneSolution Sage
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