Calcuate the closest less than date

[kreese]

Hi All, I am in need of some assistance.

[Charge Activity] File

I am working with an imported excel file that lists all labor charges which is broken out by [Charge Date], [Charge Code], and  [Employee ID].

[Rate Information] File

I have another imported excel file that contains all the rate information for each employee.  The Rate information file is broken out by [Employee ID], [Rate effective date], and [Rate].  This means that there are muliple entries for each employees rate as it has changed over the course of their employment.

I have created a relationship between these two files by the [Employee ID].

I am currently in need of a method for how to find the corresponding [Rate Information] for each [Charge Activity] line item.  My thought process was to create a dax formula that would calcuate the corresponding [Rate] for the [Rate Effective Date] that is the closest date less than (or equal to) the [Charge Date].  This way the rate is calculated correctly for that particular line item.

Any suggestions are appreciated!!

[kreese]

Additional Note:

I am looking into the possible of using the Calculate function and nesting the Filter function in the calculation as well.  Any guidance on using this method would be appreciated.

[greggyb]

// DAX
// Calculated Column in 'Charge Activity' table
RateOnDate =
CALCULATE(
    VALUES( 'Rate Information'[Rate] )
    ,TOPN(
        1
        ,CALCULATETABLE(
            'Rate Information'
            ,'Rate Information'[Employee ID] = EARLIER( 'Charge Activity'[Employee ID] )
            ,'Rate Information'[Rate Effective Date] <= EARLIER( 'Charge Activity'[Charge Date] )
        )
        ,'RateTable'[RateEffectiveDate]
        ,DESC
    )
)

CALCULATE() allows us to set a filter context in which to evaluate an expression. Argument 1 is the expression to evaluate. Arguments 2..N define filter contexts that are combined in a logical and to evaluate Argument 1 in.

Argument 1 is VALUES( 'Rate Information'[Rate] ). VALUES() returns the distinct values in a field based on filter context. If the number is distinct values is <= 1, then the result of VALUES() can be coerced to a scalar.

TOPN() returns the top N rows of some table based on sort criteria we define. We want to return one row of the table defined in CALCULATETABLE() sorted by [RateEffectiveDate] in descending order.

CALCULATETABLE() does for table expressions what CALCULATE() does for scalar expressions. We want to return a subset of 'Rate Information'. The first filter we apply to 'Rate Information' is to return only values where [Employee ID] matches the [Employee ID] in 'Charge Activity', where we are defining this calculated column. EARLIER() allows us to reach back to the row context in 'Charge Activity' from inside the CALCULATETABLE().

The second filter argument (third overall argument) to CALCULATETABLE() does a similar pattern, but based on the value of [Charge Date] in the row context of 'Charge Activity'.

Using the result of this TOPN() as a filter, CALCULATE() will return only the [Rate] that matches the criteria you defined in your post.

A relationship is unnecessary between these tables for this calculated column to work.

[Anonymous]

This was a really good trick! In my case it works really well, but sometimes there is a "draw". Meaning there are several records with the same date. Is it possible to add an Average component to your model to cope with this type od situations? Meaning in the case there is more than one record as a result of the filter and ranking - the result is the average between them? Many thanks!

[Anonymous]

Just figured it out. For me it is a good solution to wrap the whole TopN part in a FirstNonBlank statement, even if it was not exactly what I asked for.

[kreese]

Thank you so much for your response.  I'm receiving this error.  Here is how I wrote out the calculation.

EARLIER/EARLIEST refers to an earlier row context which doesn't exist

// DAX
// Calculated Column in ‘Charge Activity Report’ table
RateOnDate =
CALCULATE(
    VALUES( 'Rate Information'[Rate] )
    ,TOPN(
        1
        ,CALCULATETABLE(
            'Rate Information'
            ,'Rate Information'[Employee ID] = EARLIER( ‘Charge Activity Report’[Employee Number] )
            ,'Rate Information'[Rate Effective Date] <= EARLIER( ‘Charge Activity Report’[Charge Date] )
        )
        ,'RateTable'[Rate Effective Date]
        ,DESC
    )
)

[greggyb]

Are you defining this as a measure or a calculated column?

[kreese]

I'm not sure what happened here, but for some reason, my data errored out and I'm receiving the below error now.  Any experience you can share with this error is appreciated.

A table of multiple values was supplied where a single value was expected.

[kreese]

It worked!!! Thank you so much!!!

[kreese]

I was defining it as a measure, but I can try calculated column within the Edit Queries Mode.