GET THE CUSTOMER NAMES WITH SALES FOR THE LAST HOUR

[Anonymous]

Hi,

I have the following data 

orders sales customername hour 

                                             1:00:00 am 

                                            2:00:00 am 

                                            3:00:00 am 

                                            4:00:00 pm 

                                            6:00:00 pm

Orders in the Last Hour = CALCULATE

(count(OpenShipments[Release]),

FILTER(OpenShipments,

OpenShipments[Order Hour]=MAX(OpenShipments[Order Hour])

))

This is fetching the perfect count for the last hour. 

now i want to see the customers, sales details for the last hour 

sales in the Last Hour = CALCULATE

(SUM(OpenShipments[MDSE]),

FILTER(OpenShipments,

OpenShipments[Order Hour]=MAX(OpenShipments[Order Hour])

))

but when i add customers, it gives the sales for all the customers( irrelevant for the hours) 

please let me know what i am doing wrong . 

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

The problem you are running into is, by adding customer into your visual, you introduce a filter context that is causing your MAX('OrderShpments'[Order Hour]) to look at only the rows for the customer.  The reason the total is correct is because, on that row, there is no customer to change your filter so the right MAX('OrderShpments'[Order Hour]) is coming through.

Try something like this.

Sales in Last Hour =
VAR LastHour = CALCULATE ( MAX ( 'OpenShipments'[Order Hour] ), ALL ( 'OpenShipments' ) )
RETURN
    CALCULATE (
        SUM ( OpenShipments[MDSE] ),
        'OpenShipments'[Order Hour] = LastHour
    )

The ALL in CALCULATE ( MAX ( 'OpenShipments'[Order Hour] ), ALL ( 'OpenShipments' ) ) sets the LastHour VAR to be the highest hour across the whole table, regardless of customer.

I have attached my sample file for you to look at.

[Anonymous]

@jdbuchanan71 

Thank you for your solution !! it worked 🙂 

Great explanation.