Prefer Order Time (Hour of the day)

[MAAbdullah47]

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.

[MudassirAli]

@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 .

How To Mask Sensitive Data In Power BI

Thanks.

[EnterpriseDNA]

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.

[MAAbdullah47]

Hi @MudassirAli

Here is a link for data , I don’t have pbix file.
https://drive.google.com/file/d/10ZtoD5goQyRg7XcrtpcKNmrneQ9SG0Kg/view?usp=sharing

[MudassirAli]

@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

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

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)

[EnterpriseDNA]

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!

[MAAbdullah47]

Hi @MudassirAli
Thank you so much for your excellent work.

[EnterpriseDNA]

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!