Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Austen229022
Helper I
Helper I

Finding Most Amount of Times a Value Appears

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

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

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.

View solution in original post

6 REPLIES 6
123abc
Community Champion
Community Champion

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.

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'), ...))

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]))



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))

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.

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?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.