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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.