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

Need Help

4 Replies

  • 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.

         

         

         

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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