Return the sum based on two dates

[John FetherolfParticipant Started: 4Replies: 9Total: 13]

I want to calculate the sales for each of our sales reps during their first 90 days with the company. In the table [Sales Rep Data] I have a column called [Hire Date]. In the table [Order Header] I have a column called [Invoice Date]. In the table [Order Detail] I have a column called [Sales].

When the [Invoice Date] – [Hire Date] is less than 91, I want the sum of the sales. Here is what I tried: First 90 Day Sales=CALCULATE([Sales],(‘Order Header'[Invoice Date]-‘Sales Rep Data'[Hire Date]) < 91)

But it returns this error message: Calculation error in measure ‘Order Item Detail'[90 Days Sales]: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

John,

There are many ways to create filters in DAX, but the means you have chosen (a “top level filter” inside CALCULATE) is meant only to filter a single column. It has a lot to do with the data model is based on a columnar (not a row-based) database.

You mention that [Sales] is a column, but you use it like it is a measure (if a column and not a measure, you need to tell the formula engine how to aggregate the data, for example SUM ( ‘Order Detail'[Sales] ).

If you are using Power Pivot (and not Power BI Desktop with bi-directional filtering set) you might also have a problem with a calculation using columns from two tables that are probably not directly related to each other.

Without seeing your model, and also assuming that [Sales] is a column in the ‘Order Detail’ table (not a measure), you will probably get better results with a measure like:

First 90 Day Sales =
CALCULATE (
    SUM ( 'Order Detail'[Sales] ),
    FILTER (
        'Order Detail',
        RELATED ( 'Order Header'[Invoice Date] )
            - RELATED ( 'Sales Rep Data'[Hire Date] )
            < 91
    )
)

[John FetherolfParticipant Started: 4Replies: 9Total: 13]

Thanks – that worked perfect.

I thought I could easily modify this for the second part of my project – sales for days 91 thru 180. How would I create a second measure called [Sales Days 91 thru 180)

 

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

In this case, are you using Excel 2013, 2016 or Power BI Desktop?

[John FetherolfParticipant Started: 4Replies: 9Total: 13]

I’m using Excel 2016

[John FetherolfParticipant Started: 4Replies: 9Total: 13]

here is an image of the data model diagram view. There is around 5Million order header records and over 50Million Order Item detail records

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Excel 2016 allows for variables, which often allows formulas to execute faster.

Try this:

Sales for Days 91 Thru 180 :=
CALCULATE (
    SUM ( 'Order Detail'[Sales] ),
    FILTER (
        'Order Detail',
        VAR vDaysSinceHire = 1.0 * ( RELATED ( 'Order Header'[Invoice Date] ) - RELATED ( 'Sales Rep Data'[Hire Date] ) ) RETURN
        AND ( vDaysSinceHire >= 91, vDaysSinceHire <= 180 )
    )
)

The line that starts with VAR and ends with RETURN is the definition of the variable.

The line with AND could have been written instead (without the AND) as

vDaysSinceHire >= 91 && vDaysSinceHire <= 180

The advantage here of using a variable is the days since hire would be calculated once instead of twice for every row in the order detail table.

[Author]

Posts