Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find Max Day per Week

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
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        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