cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

GET THE CUSTOMER NAMES WITH SALES FOR THE LAST HOUR

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 . 
 
sales.jpg
 
 
 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: GET THE CUSTOMER NAMES WITH SALES FOR THE LAST HOUR

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.

View solution in original post

2 REPLIES 2
Super User
Super User

Re: GET THE CUSTOMER NAMES WITH SALES FOR THE LAST HOUR

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.

View solution in original post

Anonymous
Not applicable

Re: GET THE CUSTOMER NAMES WITH SALES FOR THE LAST HOUR

@jdbuchanan71 

 

Thank you for your solution !! it worked 🙂 

Great explanation. 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,745)