cancel
Showing results for
Did you mean:

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

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,""))

2 ACCEPTED SOLUTIONS
Super User
`ast Year Total = CALCULATE(SUM(Query1[Total Sale]),Query1[Year] = 2016)`

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Resolver III

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

7 REPLIES 7
Resolver III

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

Resolver III

@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?

Helper I

yes thanks for you help

Helper I

I dont have dates Columns just have month and year column

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Resolver III

@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?

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors