Max Weekday

[New2This]

Good morning,

I want to use a measure in the Data Card visual to calculate the weekday that most cases in my company happen but when I filter by year it does not calculate the max weekday correctly. This is the formula I have:

_Max Day = FORMAT(MAXX(

    DISTINCT(Data[DOTW]),

    CALCULATE(COUNT(Data[DOTW]),Data[DOTW] = EARLIER(Data[DOTW]))),"DDDD")

It works but when I add a year filter to the data card it filters wrong.

[Anonymous · Accepted solution]

Thanks for the reply from audreygerred , please allow me to provide another insight: 
Hi  @New2This ,

Here are the steps you can follow：

1. Create measure.

Test =
var _table=
SUMMARIZE(
    ALLSELECTED('Data'),
    [DOTW],"Count",COUNTX(FILTER(ALLSELECTED('Data'),'Data'[DOTW]=EARLIER('Data'[DOTW])),[DOTW]))
RETURN
FORMAT(
    MAXX(
    FILTER(_table,[Count]=MAXX(_table,[Count])),[DOTW]),"dddd")

2. Result:

Best Regards,

Liu Yang

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

[audreygerred]

Hi! Here is what I used:

DayWithHighestRowCount =

VAR DayCounts =

    ADDCOLUMNS(

        SUMMARIZE(

            'Date',

            'Date'[DOTW Name]

        ),

        "RowCount", CALCULATE(COUNTROWS('Sales'))

    )

VAR MaxDay =

    TOPN(

        1,

        DayCounts,

        [RowCount],

        DESC

    )

RETURN

    MAXX(MaxDay, 'Date'[DOTW Name])

It works regardless if I have a year filtered or not. No years - Tuesday is the highest:

2018 was Friday: