Prefer Order Time (Hour of the day)

Dear Experts
I need to calculate the Best Order Time or Preferred time (Hour) (per day - week - month - year) how can we do this in Dax?

Note: The preferred means the Highest Number of orders.

@MAAbdullah47

It’s difficult to provide a solution without a source and pbix file.

Please attach the pbix files or if you have sensitive data, you can look at the video by our expert @BrianJ showing to mask the sensitive data .

Thanks.

Thanks for posting your question @MAAbdullah47. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Hi @MudassirAli

Here is a link for data , I don’t have pbix file.

@MAAbdullah47

If I understand it correctly, you need the Highest # of orders that can be filtered by (per day - week - month - year). I have a solution based on the assumption I mentioned.

I added a continuous date table in the model that is prepared by @Melissa

Then in the data you provided, separated the Hour from Order Time in Power Query.

You have duplicate Order Numbers but they are generated on a different time frame and different Products are ordered on the same order number that’s why didn’t count duplicate order numbers as ONE instead treated every Order as Unique. Moreover, in the dataset provided, many Order Dates are missing that’s why the slicers are showing Blanks also.

The measure to calculate the Highest # of orders by Hour is

Max Orders by Hour =
VAR vTable =
    CALCULATETABLE (
        ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Hour] ), "@Orders", [# Of Orders] ),
        ALLSELECTED ()
    )
VAR MaxOrders =
    MAXX ( vTable, [@Orders] )
VAR Result =
    IF ( [# Of Orders] = MaxOrders, MaxOrders, BLANK () )
RETURN
    Result

The final result is as follows:

Attaching the PBIX file and source file.

Let us know if this is what you are looking for.

Thanks.

Prefer Order Time Solution.pbix (1.9 MB)

1 Like

Hi @MAAbdullah47, did the response provided by @MudassirAli help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

1 Like

Hi @MudassirAli
Thank you so much for your excellent work. :+1: :handshake:

Hi @MAAbdullah47, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!