How to summarize a grouped date with the highest value

[AmazingRandom]

This is a very new experience to me and after a few attempts I am out of ideas on how to achieve this.

Essentially, I need to show the highest condition for the day, however our data is being captured hourly, so I need to group the timestamps by date. That first part I managed to do easily. The second bit is where I'm stuck. 

Above is a screenshot of a scenario. Ignore the Manual and Current non compliant columns as the condition column already calculates the final values I need to summarise. So for this instance, the whole day should be flagged as 1, how can I make a new column that will look at the highest condition valueof the day and return it, this means the new column should only have the value 1 across all rows. Thanks in advance

[Ashish_Mathur]

Hi,

Write this calculated column formula

Highest condition value = calculate(max(Data[Condition]),filter(Data,Data[Date]=earlier(Data[Date])))

Hope this helps.

[AmazingRandom]

It didn't work, I'm getting the highest value possible in all rows, which is 2

[samratpbi · Accepted solution]

Hi, considering that you would need a column, I created this sample dataset and created a conditional column:

Below is the column calculation:

MaxCond =

CALCULATE(

    MAX(DateCond[Condition]),

    ALLEXCEPT(

        DateCond,

        DateCond[orderdate]

    )

)

As you can see 05/03/2024 has max condition 1, 06/03 has 0 and 07/03 has 2

If this resolves your problem, then please accept this as solution to help others. Thanks!

[AmazingRandom]

It worked, I had to edit the formula to also filter by location. I got a follow up question, how can I aggregate that into one single value for count? Off your example, there would be a count for each condition. Though it counts only one instance. So there would be 

0 = 1 day

1 = 1 day

2 = 1 day