Find Max Day per Week

[Anonymous]

Hello - I have a database where each time a punch is read it counted and put in as a row. Total for that day is the number of rows added (does have a date of punch column)

I want to find what day has the most punches for the week, how can this be done?

Using 

Punches = CALCULATE(COUNTROWS('Sum Log'),FILTER(ALL('Sum Log'),'Sum Log'[Week Ending Date]=MAX('Sum Log'[Week Ending Date]))

Gives the total for the week

[Greg_Deckler]

So do you have something that tells you the day in the data? Sample data would assist greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Greg - the data is set like this

Date       Punch    WeekEnd

2/1/20   B               2/8/20

2/1/20   B               2/8/20

2/1/20   B               2/8/20

2/1/20   B               2/8/20

2/2/20   B               2/8/20

2/2/20   B               2/8/20

[v-gizhi-msft]

Hi,

According to your description, i create a table to test:

Please try to create this measure:

Measure = 
var a = TOPN(1,SUMMARIZE('Table','Table'[Date],"Count",COUNTROWS('Table')),MAX([Count]),DESC)
var maxDate = MAXX(a,'Table'[Date])
return
DATE(YEAR(maxDate),MONTH(maxDate),DAY(maxDate))

Put this measure into Card visual, the result shows the max count of Punches forthe week:

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Sorry, I explained this poorly.

I want to graph the highest value per work on a bar graph rather than displaying the date

Max Percentage measure =
VAR MaxYear =
    CALCULATE (
        MAX ( 'User Table'[Year] ),
        ALLEXCEPT ( 'User Table', 'User Table'[Category] )
    )
RETURN
    CALCULATE (
        MAX ( 'User Table'[Percentage] ),
        FILTER (
            ALLEXCEPT ( 'User Table', 'User Table'[Category] ),
            'User Table'[Year] = MaxYear
        )
    )

 I Tried this and it didn't work as intended - only displaying the saturday value

[v-gizhi-msft]

Hi,

I add some data to my original test file.

Please try this measure:

Measure = 
var a = TOPN(1,SUMMARIZE('Table','Table'[Date],"Count",COUNTROWS('Table')),MAX([Count]),DESC)
var maxDate = MAXX(a,'Table'[Date])
return
DAY(maxDate)

Then choose [WeekEnd] column and this measure, the result shows:

Best Regards,

Giotto Zhi

[Anonymous]

It's only getting the max value for the last day of the week. I'm looking to take the max per week, not just the max at the end of the week

[v-gizhi-msft · Accepted solution]

Hi,

I think you have misunderstood my data, and the last day of each week only represents this week and the max value is not the end day's value.

So i add an column to the original table:

Week = SWITCH('Table'[WeekEnd],DATE(2020,2,8),"Week-1",DATE(2020,2,15),"Week-2",DATE(2020,2,22),"Week-3")

And i think you should already have the column like the above to represent each week.

The result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[Anonymous]

I'm still not getting correct results

[amitchandak]

If you are looking for only max value per week, then rank can help.

rankx(all('User Table'[Week end date]),MAX ( 'User Table'[Percentage] ),,desc,dense)

How to filter on rank:https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...

If you need a max value on the max date of the week , that is a different context

[Anonymous]

Thank you, this worked great. 

How can I get the actual number rather than the date? thanks!