Filter Formula

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"; 

I dont have dates Columns just have month and year column

---

McCow wrote:

 

 

Last Year Total = CALCULATE(SUMX(Query1;Query1[Total Sale]);FILTER(Query1;Query1[Year] = 2016))

 

---

this formula must be relevant for you. Or not?