getting error
Feedback Type:
Frown (Error)
Timestamp:
2017-12-18T16:15:20.0032165Z
Local Time:
2017-12-18T16:15:20.0032165+00:00
Session ID:
56d36885-d1df-408e-8014-d3c583d17c00
Release:
December 2017
Product Version:
2.53.4954.481 (PBIDesktop) (x64)
Error Message:
Something's wrong with one or more fields: (Query1) Last Year Total: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.
Formulas:
section Section1;
shared Query1 = let
Source = Sql.Database("10.0.1.9", "EnergyCRM_Main", [Query="#(lf)DECLARE @STARTDATE varchar(50) SET @STARTDATE = '2015/12/22 00:00:00' #(lf)--DECLARE @ENDDATE DATETIME SET @ENDDATE = '2017/11/21 23:59:59' #(lf) DROP TABLE IF EXISTS #SALE#(lf)#(lf) SELECT NAME#(lf) ,CASE WHEN DATEPART(DAY, SALEDATE) > 21 THEN DATENAME(MONTH, DATEADD(MONTH, 1, SALEDATE))#(lf) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 1, SALEDATE))#(lf) ELSE DATENAME(MONTH, SALEDATE) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 0, SALEDATE))#(lf) END ""NEW BUSNIESS MONTH""#(lf)#(tab) ,AVG(CL.CONTRACTLEN) ""AVG NEW CONTRACT""#(lf)#(lf),AVG(UPLIFT) ""AVG NEW UPLIFT""#(lf),COUNT(NAME) ""NEW BUSNIESS TOTAL""#(lf),CONCAT('£', CAST(CONVERT(VARCHAR, CAST(CAST(SUM((CL.CONTRACTLEN)/12 * S.CONSUMPTION * UPLIFT)/100 AS DECIMAL(10,2)) AS MONEY), 1)#(lf) AS VARCHAR)) ""NEW BUSNIESS SALE VALUE""#(lf)#(lf) INTO #SALE#(lf) FROM ACTION A#(lf) JOIN SALE S ON S.SALEID = A.OBJECTID#(lf) INNER HASH JOIN USERS U ON S.SALEUSER = U.USERID#(lf) INNER HASH JOIN CONTRACTLENGTH CL ON S.CONTRACTLENID = CL.CONTRACTLENGTHID#(lf) INNER HASH JOIN ACTIONSTATUS ASS ON ASS.ACTIONSTATUSID = A.ACTIONSTATUSID#(lf) INNER JOIN CONTRACT C ON C.CONTRACTID = S.NEWCONTRACTID#(lf) INNER JOIN METERCONTRACT MC ON MC.CONTRACTID = C.CONTRACTID#(lf) INNER JOIN METER M ON M.METERID = MC.METERID#(lf) #(tab)WHERE #(lf)#(tab)---CREATEDBYUSERID = 1121 #(lf) --- AND #(lf)S.SALEDATE >= @STARTDATE ---AND @ENDDATE#(lf)#(tab) AND ACTIONTYPEID = 27#(lf)#(tab) AND A.ACTIONSTATUSID = 1#(lf)#(tab)AND U.USERID NOT IN ('2236','33')#(lf)AND S.SALETYPE IN ('1','4','2','6')#(lf)GROUP BY NAME,CASE WHEN DATEPART(DAY, SALEDATE) > 21 THEN DATENAME(MONTH, DATEADD(MONTH, 1, SALEDATE))#(lf) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 1, SALEDATE))#(lf) ELSE DATENAME(MONTH, SALEDATE) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 0, SALEDATE))#(lf) END #(lf)#(lf)#(tab) ;#(lf)#(lf)#(tab) WITH BASETABLE AS (#(lf)#(lf) SELECT U.NAME AGENT,#(lf)#(lf) --- TOTAL SALE MONTH 22/12/2016 AS JAN 2017#(lf)#(tab)#(tab)#(tab) CASE WHEN DATEPART(DAY, SALEDATE) > 21 THEN DATENAME(MONTH, DATEADD(MONTH, 1, SALEDATE))#(lf) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 1, SALEDATE))#(lf) ELSE DATENAME(MONTH, SALEDATE) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 0, SALEDATE))#(lf) END ""MONTH""#(lf),AVG(CL.CONTRACTLEN) ""TOTAL AVG CONTRACT""#(lf),ISNULL(convert(varchar(10),TS.[AVG NEW CONTRACT]),'-') ""NEW BUSNIESS CONTRACT""#(lf),AVG(UPLIFT) ""TOTAL AVG UPLIFT""#(lf),ISNULL(convert(varchar(10),TS.[AVG NEW UPLIFT]),'-') ""NEW BUSNIESS UPLIFT"" #(lf),COUNT(SALEID) ""TOTAL SALE""#(lf),ISNULL(CAST(TS.[NEW BUSNIESS TOTAL] AS VARCHAR(20)),'NO SALE') ""NEW BUSNIESS TOTAL""#(lf),CONCAT('£', CAST(CONVERT(VARCHAR, CAST(CAST(SUM((CL.CONTRACTLEN)/12 * S.CONSUMPTION * UPLIFT)/100 AS DECIMAL(10,2)) AS MONEY), 1) AS VARCHAR))#(lf) ""TOTAL SALE VALUE""#(lf),ISNULL(TS.[NEW BUSNIESS SALE VALUE],'NO SALE') ""NEW BUSNIESS SALE VALUE""#(lf) FROM ACTION A#(lf) JOIN SALE S ON S.SALEID = A.OBJECTID#(lf) INNER HASH JOIN USERS U ON S.SALEUSER = U.USERID#(lf) INNER HASH JOIN CONTRACTLENGTH CL ON S.CONTRACTLENID = CL.CONTRACTLENGTHID#(lf) INNER HASH JOIN ACTIONSTATUS ASS ON ASS.ACTIONSTATUSID = A.ACTIONSTATUSID#(lf) INNER JOIN CONTRACT C ON C.CONTRACTID = S.NEWCONTRACTID#(lf) INNER JOIN METERCONTRACT MC ON MC.CONTRACTID = C.CONTRACTID#(lf) INNER JOIN METER M ON M.METERID = MC.METERID#(lf) LEFT JOIN #SALE TS ON TS.NAME = U.NAME AND TS.[NEW BUSNIESS MONTH] = CASE WHEN DATEPART(DAY, SALEDATE) > 21 THEN DATENAME(MONTH, DATEADD(MONTH, 1, SALEDATE))#(lf) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 1, SALEDATE))#(lf) ELSE DATENAME(MONTH, SALEDATE) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 0, SALEDATE))#(lf) END#(lf) #(tab)WHERE #(lf)S.SALEDATE >= @STARTDATE ---AND @ENDDATE#(lf)#(tab) AND ACTIONTYPEID = 27#(lf)#(tab) AND A.ACTIONSTATUSID = 1#(lf)#(tab)AND U.USERID NOT IN ('2236','33')#(lf)GROUP BY U.NAME,TS.[NEW BUSNIESS SALE VALUE],TS.[NEW BUSNIESS TOTAL],#(lf)CASE WHEN DATEPART(DAY, SALEDATE) > 21 THEN DATENAME(MONTH, DATEADD(MONTH, 1, SALEDATE))#(lf) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 1, SALEDATE))#(lf) ELSE DATENAME(MONTH, SALEDATE) + ' ' + DATENAME(YEAR, DATEADD(MONTH, 0, SALEDATE))#(lf) END ,#(lf)#(tab) TS.[NEW BUSNIESS MONTH],TS.[AVG NEW CONTRACT]#(lf),TS.[AVG NEW UPLIFT]#(tab), TS.[NEW BUSNIESS MONTH] #(lf))#(lf)SELECT bs.Agent,bs.[New Busniess Sale Value],bs.[Total Sale Value]#(lf),bs.[New Busniess Total],bs.[Total Sale],#(lf)bs.[New Busniess Uplift],bs.[Total Avg Uplift],bs.[New Busniess Contract],bs.[Total Avg Contract]#(lf),convert(char(3),bs.Month) ""Month"", Year(bs.month) ""Year"",'Q' + ' ' + convert(varchar,datepart(qq,bs.MONTH)) ""Quarter""#(lf)#(lf)FROM BASETABLE BS"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"New Busniess Sale Value", type number}, {"Total Sale Value", type number}, {"New Busniess Total", Int64.Type}, {"New Busniess Uplift", type number}, {"New Busniess Contract", Int64.Type}})
in
#"Changed Type";
