Calculated column in DAX to show current BusinessArea

Question

I have a table in my SSAS-model with SCD-type2 functionality.

CustNr      StartDate      EndDate       BusinessArea
123         2014-12-01     2015-01-01    Norway
123         2015-01-01     -             Sweden

I need a calc-column(DAX) which shows the current BusinessArea(based on customer number). How do i approach it? I've heard about the "Earlier" function but i am new to DAX and cannot get my head around it.

The desired output would be like this:

CustNr      StartDate      EndDate       BusinessArea      CurrentBA
123         2014-12-01     2015-01-01    Norway            Sweden
123         2015-01-01     -             Sweden            Sweden

All answers are welcome! Cheers!

Answer 1

LOOKUPVALUE()

(edit: note original left for continuity - correct measure below in edit section)

CurrentBusinessArea =
LOOKUPVALUE(
    DimCustomer[BusinessArea] // Lookup column - will return value
                              // matching search criteria below.
    ,DimCustomer[CustNr]      // Search column 1.
    ,DimCustomer[CustNr]      // Value to match to search column 1 -
                              // this is evaluated in row context.
    ,DimCustomer[EndDate]     // Search column 2.
    ,"-"                      // Literal value to match for search
                              // column 2.
)

I doubt that your [EndDate] is actually a text field, so I also doubt that the literal value for [EndDate] for the row that represents the current business area is actually "-". If it's blank, use the BLANK() function rather than a literal "-".

Edit based on comment, corrected measure definition with some discussion:

CurrentBusinessArea =
LOOKUPVALUE(
    DimCustomer[BusinessArea]
    ,DimCustomer[CustNr]
    ,DimCustomer[CustNr]
    ,DimCustomer[EndDate]
    ,DATE(BLANK(),BLANK(),BLANK())
)

Normally in DAX you can test directly for equality to BLANK(). It tends not to act similarly to NULL in SQL. In fact, you can create a column to test this. If you do any of these, they return true for the row with a blank [EndDate]:

=DimCustomer[EndDate] = BLANK()
=ISBLANK(DimCustomer[EndDate])
=DimCustomer[EndDate] = 0  //implicit conversion 0 = blank

For some reason there is an issue in the conversion from Date/Time to BLANK(). The construction above, using the DATE() function fed with all BLANK()s works for me. I had assumed that LOOKUPVALUE() would work with a literal blank (fun fact, if data type is Integer, LOOKUPVALUE() works with a BLANK()). Apologies on that.