Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
skorpion
Helper I
Helper I

Filter Formula

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 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

ast Year Total = CALCULATE(SUM(Query1[Total Sale]),Query1[Year] = 2016)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

McCow
Resolver III
Resolver III

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

View solution in original post

7 REPLIES 7
McCow
Resolver III
Resolver III

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

Greg_Deckler
Super User
Super User

ast Year Total = CALCULATE(SUM(Query1[Total Sale]),Query1[Year] = 2016)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.