Forum Discussion

erikboderek's avatar
erikboderek
Regular Visitor
9 years ago

Filter by Year into a Date Field

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 wit...
Need Help
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

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's avatar
erikboderek
Regular Visitor
9 years ago

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's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    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.