Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
im trying to get the last year value and im getting blank output
im using this formula
Last Year Total = CALCULATE(SUM(Query1[Total Sale]),IF(Query1[Year] = 2016,""))
please help me out where im wrong
Solved! Go to Solution.
ast Year Total = CALCULATE(SUM(Query1[Total Sale]),Query1[Year] = 2016)
Hi @skorpion
try this:
Last Year Total = CALCULATE(SUMX(Query1;Query1[Total Sale]);FILTER(Query1;Query1[Year] = 2016))or without extra column [Year]
Last Year Total = CALCULATE(SUM(Query1[Total Sale]);FILTER(Query1;YEAR(Query1[Dates]) = 2016))
Best regs
Hi @skorpion
try this:
Last Year Total = CALCULATE(SUMX(Query1;Query1[Total Sale]);FILTER(Query1;Query1[Year] = 2016))or without extra column [Year]
Last Year Total = CALCULATE(SUM(Query1[Total Sale]);FILTER(Query1;YEAR(Query1[Dates]) = 2016))
Best regs
@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?
yes thanks for you help
I dont have dates Columns just have month and year column
ast Year Total = CALCULATE(SUM(Query1[Total Sale]),Query1[Year] = 2016)
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";
@skorpion wrote: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.
sorry, how corresponded this error with your question?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |