Get the Month with most no of complaints

[Govind98]

Hi, 
I was working on a measure that will return the month with most complaints from the desired time range. I have created a  DateTable with columns Month, MonthName,MonthYear(eg: Jan 2019),Year,Quarter. In the dashboard I have provided the slicer for MonthYear as dropdown style which will be the anchor date. Then provided a parameter with numeric range for the user to enter the value for N. So The timeperiod should be from (Anchor date- N) to Anchor date. I want to create a card in order to display the month with most no of complaints along with year (eg: Jan 2024). For counting the complanit, you can use complaint_id column in the main table. 
DateTable:

Expected Dashboard.

I have searched online and tried chatGPT, but I cant get any answers

[johnt75 · Accepted solution]

Try

Month with Max Complaints =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR ChosenMonths =
    SELECTEDVALUE ( 'Previous N Months'[Value] )
VAR StartDate =
    EOMONTH ( ReferenceDate, - ( ChosenMonths + 1 ) ) + 1
VAR MonthsWithValues =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Date'[Year month] ),
            DATESBETWEEN ( 'Date'[Date], StartDate, ReferenceDate )
        ),
        "@num complaints", CALCULATE ( DISTINCTCOUNT ( 'Table'[complaint_id] ) )
    )
VAR Result =
    CONCATENATEX (
        TOPN ( 1, MonthsWithValues, [@num complaints] ),
        'Date'[Year month],
        ", "
    )
RETURN
    Result

In the event of multiple months having the same highest number it will return them all in a comma separated list.

[Govind98]

I think it is returning the value as a table as in the image. I made some changes in the code. Name of the date table is DateTable and column name is MonthYear. Also the parameter name is N, so the value will be 

N[N Value].

[johnt75]

That looks like its working then.