Power BI Measure returning Blank when it shouldn't

Question

I am trying to create a measure that will return a value for the number of issued receipts in the year 2020:

CALCULATE(SUM(Receipts[issued]), FILTER(Receipts, Receipts[Year] = 2020))

However, the measure keeps returning blank, even as a calculated column.

I've also tried

CALCULATE(SUM(Receipts[issued]), FILTER(Receipts, Receipts[Year].Year = 2020))

But that is returning an error saying the syntax for "Year" is incorrect.

The Year column is of datatype Date and is linked to a dimDate table on the Date column.

I am trying to retrieve the value 440,000. What am I doing wrong?

What is the error shown? What is the datatype of Receipts[Year]? — davidebacci
– davidebacci, Commented Jun 26, 2022 at 16:21
As stated in the question, there is no error in the first DAX example, it's just a blank value, and the second says "The syntax for "Year" is incorrect". The datatype is Date. — Sweepster
– Sweepster, Commented Jun 26, 2022 at 16:23
There is your problem. If the datatype is date, then "= 2020" is not a date. What is the actual value in the column if the date is date. Have you tried "YEAR(Receipts[Year]) = 2020" — davidebacci
– davidebacci, Commented Jun 26, 2022 at 16:28
That did it. I really wish Power BI's error reporting would be smart enough to see that as a solution. Thank you. — Sweepster
– Sweepster, Commented Jun 26, 2022 at 16:33
Good stuff. I'll add it as an answer so you can mark as solved. BTW, you should not filter entire tables. I'll give you a better syntax. — davidebacci
– davidebacci, Commented Jun 26, 2022 at 16:34

davidebacci · Accepted Answer · 2022-06-26 16:35:24Z

2

Try this.

CALCULATE(SUM(Receipts[issued]), YEAR(Receipts[Year]) = 2020)

answered Jun 26, 2022 at 16:35

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1