Finding Most Amount of Times a Value Appears

[Austen229022]

Currently there is a slicer that is showing the last 1 month of data, this counts all the dates in the Date Received column to find out how many times dates within that range appear.

What I am wanting to achieve is count how many times a certain date appears and show the most times that it occurs

EG:

Date

01/01/2023

01/01/2023

04/01/2023

14/01/2023

This would show that the most times that a date appears (01/01/2023) is 2

[123abc · Accepted solution]

To count how many times a certain date appears and show the most times it occurs in Power BI, you can use a combination of measures and visuals. Here's how you can achieve this:

1. Create a New Measure:

   • Go to the "Modeling" tab in Power BI.
   • Click on "New Measure."
   • Use the following DAX formula to count the occurrences of each date in your Date column:

Date Count = COUNTROWS(FILTER(YourTable, YourTable[Date] = MAX(YourTable[Date])))

1. Replace "YourTable" with the name of your data table and "Date" with the name of your date column.

2. Create a Table Visual:

   • Add a table visual to your report.
   • In the table visual, add the "Date" column and the "Date Count" measure you just created.

3. Sort the Table Visual:

   • Click on the "Date Count" column in the table visual.
   • In the "Visualizations" pane, under "Sort," select "Descending."

4. Display the Top Date:

   • To display the date with the highest count, you can create another measure to fetch the corresponding date. Create a new measure with the following DAX formula:

Most Frequent Date =
VAR MaxCount = MAX(YourTable[Date Count])
RETURN
CALCULATE(MAX(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MaxCount))

1. Replace "YourTable" with the name of your data table.

2. Display the Most Frequent Date:

   • Add a card visual to your report.
   • In the card visual, add the "Most Frequent Date" measure you just created.

Now, when you use the slicer to select the last 1 month of data, the table visual will display the dates and their respective counts in descending order, showing the date with the most occurrences at the top. The card visual will display the most frequent date.

This setup allows you to dynamically find and display the date that appears the most within the selected date range.

[Austen229022]

Is there a way that I can use this to also have it filter based on another column as well? Im needing to filter on the date while also filtering on whether a Regime column is one of two items

Something like:

SUMMARIZE('Applications','Applications'[Date Filter],"COUNT",AND(COUNTROWS('Applications'), ...))

[Austen229022]

I currently have a measure that finds the date that comes up the most, though now I am not able to get a COUNT on it whether using COUNTROWS/COUNTX etc

my measure to find the date is as follows:

Measure - Max Date Count =

VAR T1 =

        SUMMARIZE('EW Applications','EW Applications'[Date Received],"COUNT",COUNTROWS('EW Applications'))

VAR MostRepeated =

    MAXX(T1,[COUNT])

Return

   MAXX(T1,IF([COUNT] = MostRepeated,'EW Applications'[Date Received]))

[Austen229022]

Thank you for the reply!

Do you know if this would also work for finding the lowest amount of times a value occurs?

eg: Changing the MAX in the DAX code to MIN like below

Least Frequent Date =
VAR MinCount = MIN(YourTable[Date Count])
RETURN
CALCULATE(MIN(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MinCount))

[123abc]

Yes, you can modify the DAX code to find the date with the lowest count, just as you've suggested. Here's the modified DAX code:

Least Frequent Date =
VAR MinCount = MIN(YourTable[Date Count])
RETURN
CALCULATE(MIN(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MinCount))

This code will calculate the date that appears the least number of times in your data based on the "Date Count" column (assuming you have such a column representing the count of each date). Make sure to replace 'YourTable' with the actual name of your table and 'Date Count' with the actual name of your count column.

You can use the "Least Frequent Date" measure in a visual, like a Card or a Table, to display the date with the lowest count in your Power BI report.

[Austen229022]

I have done this though for whatever reason it isnt showing the least occuring though showing the first of the month (I am filtered on a month) how can I fix this?