Forum Discussion

New2This's avatar
New2This
New Member
1 year ago
Solved

Max Weekday

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    1 year ago

    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:

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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