How to retrieve a value that is on or before a certain date

[zudar]

Hi all,

I'm struggling with constructing a DAX-formula and could really use some help.

I have a table with sales data from (multiple) coffee vending machines that looks like:

Date	Machine	Product
15-4-2022	A	ERROR
18-4-2022	A	Cappuccino
23-4-2022	A	Cappuccino
24-4-2022	A	ERROR
26-4-2022	A	Espresso
etc..	etc..	etc..

I have a table with Operating Hours per date and vending machine that looks like*:

Date	Machine	Hours Operated
1-4-2022	A	2000
24-4-2022	A	2500
24-4-2022	A	2510
1-5-2022	A	2560
21-5-2022	A	2990
etc..	etc..	etc..

*Note that there are long periods without data, and also periods with multiple data points on one date.

Finally, I have a table with the vending machines:

Machine	Model
A	ZB-2.0
B	ZB-2.2
C	ZB-2.2

The end result that I want is this:

Machine	Model	Last Error Date	Hours Operated until Error
A	ZB-2.0	24-4-2022	2510
B	ZB-2.2	...	...
C	ZB-2.2	...	...

Where the DAX-formula for the 'Last Error Date'-measure in the Sales-table is written as:

Last Error Date = 
MAXX(
    TOPN( 1, FILTER (
        'Sales',
        'Sales'[Product] in {"ERROR"}
    ), 'Sales'[Date], DESC),'Sales'[Date])

For the corresponding 'Hours Operated until Error', I want to retrieve the value from the 'Operating Hours'-table, for the date that is on or before the date of the Last Error Date. If there are multiple values equally 'close to' the Last Error Date, then I want to pick the highest value.

In this case, the Last Error Date was 24-4, which means the two 'Hours Operated'-values of 2500 and 2510 qualify equally as 'closest' to the Last Error Date, but 2510 is higher so this value is returned.

How do I write the DAX-formula for this 'Hours Operated until Error'-measure?

Your help is very much appreciated! 🙂

[v-rongtiep-msft · Accepted solution]

Hi @zudar ,

I have created a simple sample, please refer to it to see if it helps you.

Create two measures.

Measure =
CALCULATE (
    MAX ( 'sales data'[Date] ),
    FILTER (
        ALL ( 'sales data' ),
        'sales data'[Product] = "ERROR"
            && 'sales data'[Machine] = SELECTEDVALUE ( machines3[Machine] )
    )
)

AMX_VALUE =
VAR _1 =
    CALCULATE (
        MAX ( 'sales data'[Date] ),
        FILTER (
            ALL ( 'sales data' ),
            'sales data'[Product] = "ERROR"
                && 'sales data'[Machine] = SELECTEDVALUE ( machines3[Machine] )
        )
    )
RETURN
    MAXX (
        FILTER ( ALL ( 'operating 2' ), 'operating 2'[Date] = _1 ),
        'operating 2'[Hours Operated]
    )

 Or two columns.

Column =
CALCULATE (
    MAX ( 'sales data'[Date] ),
    FILTER (
        ( 'sales data' ),
        'sales data'[Product] = "ERROR"
            && 'sales data'[Machine] = EARLIER ( machines3[Machine] )
    )
)

Column AMX_VALUE =
VAR _1 =
    CALCULATE (
        MAX ( 'sales data'[Date] ),
        FILTER (
            ALL ( 'sales data' ),
            'sales data'[Product] = "ERROR"
                && 'sales data'[Machine] = EARLIER ( machines3[Machine] )
        )
    )
RETURN
    MAXX (
        FILTER ( 'operating 2', 'operating 2'[Date] = _1 ),
        'operating 2'[Hours Operated]
    )

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.