Filter by Year into a Date Field

[erikboderek]

Hi Everybody,

I'm trying to build a measure which filters out values based on a year and retrieve the previous year. For example, if it's 2017, retrieve all 2016 fields. The problem I believe is with the filter. The Year field is a YYYY and the field Date is a M/D/YYYY hh:mm:ss. How do I get around this? Right now I am only retrieving a blank value. Here's my formula.

Current Year STLY =
var this_year = if(hasonevalue(Booking[Year]),
values(Booking[Year]), 2017)

var last_year = this_year - 1

var returnValue =
calculate(sum(Booking[Gross_Amount]),
filter (Booking, Booking[Date] = last_year &&
Booking[Year] = last_year)
)

return returnValue

[v-jiascu-msft]

Hi @erikboderek,

Could you please mark the proper answer as solution or share the answer if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

[TomMartens]

Hey,

I would just build a separate Calendar table, join the date column from this table with your table, so that do not have to consider the year column in your booking com.

Then it will become much more simpler to build your previous year measures. Maybe this gets you started:

http://www.daxpatterns.com/time-patterns/

Hope this helps

[erikboderek]

I do have a calendar table and there is a relationship between that table and my Booking table, though not to that fields mentioned in my post. If I were to add a relationship between from calendar table to my year, and/or date field, what would this measure then look like?

[TomMartens]

The simplest form would be

CALCULATE(SUM('ResellerSales_USD'[SalesAmount_USD]), SAMEPERIODLASTYEAR('Calendar'[DateKey]))

But this makes it necessary that DateKey column from the Calendar table is related (and the relationship is active) with the Date column from your bookings table.

If it's not possible to join the columns I guess you have to go through the link from daxpatterns I mentioned above, almost everything is treated there.