Return highest/lowest sales for different date/time intervals over filtered date range

[Rocky1001]

HI All, a newbie to Power BI, hoping for some help please!

I trying to work out best way to return max/min counts of sales over a date range but for various date/time intervals.

So the user should specify a date range and should be able to easyily identify the highest/lowest sales within different intervals within the date range, it could be max/min sales for a month, a week, day, hour or 15min bin.

Im struggling to think of the best approach, any help much appreciated!

Many thanks in advance!

MinMaxSample 

[Anonymous]

Hi  @Rocky1001 

You can refer to the following measure：

Measure = var a=SUMMARIZE(Sales,[Product],"Count",COUNTROWS(Sales))
return MAXX(TOPN(1,a,[Count],DESC),[Count])

Tip: Since the pbix file you provided cannot be opened, I calculated based on the data you provided, and I don't know which two fields of your Time table and Sales table are related, so I did not associate it in the example file, in addition to the date table I created is different from yours, the date table in the sample file is as follows.  

Best Regards!

Yolo Zhu

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

[Rocky1001]

Hi, thanks for your reply, the Time table is joined on RoundedTime fields with Sales table.

Can you please advise how I can find out which 15min Bucket within a day has the max row count from sales table and how maybe to return the rowCount and the day/bucket in question to be shown in a Card visual.

So user would filter a date range and the card would show somehting like 

Max rows(412) - 6th Feb 2023 - 13.00 - Bucket(30)

I think I need to use Summarizecolumns maybe, as I assume I need to group by the Date column in Date table but also group by Hour and 15Bucket from Time table, not sure how to achieve this.

Many thaks for any help!!!

[Rocky1001]

Hi all, I've tried to simplify my query, basically I need to show the max sales within a given Date range, by Month, day, hour and 15min bucket, would appreciate some help with the Dax for this, also any adivce how to best display this would be massively helpful!

Thanks again!

The model in PBSample of some of the data