March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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
Solved! Go to 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:
Create a New Measure:
Date Count = COUNTROWS(FILTER(YourTable, YourTable[Date] = MAX(YourTable[Date])))
Replace "YourTable" with the name of your data table and "Date" with the name of your date column.
Create a Table Visual:
Sort the Table Visual:
Display the Top Date:
Most Frequent Date =
VAR MaxCount = MAX(YourTable[Date Count])
RETURN
CALCULATE(MAX(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MaxCount))
Replace "YourTable" with the name of your data table.
Display the Most Frequent Date:
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.
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:
Create a New Measure:
Date Count = COUNTROWS(FILTER(YourTable, YourTable[Date] = MAX(YourTable[Date])))
Replace "YourTable" with the name of your data table and "Date" with the name of your date column.
Create a Table Visual:
Sort the Table Visual:
Display the Top Date:
Most Frequent Date =
VAR MaxCount = MAX(YourTable[Date Count])
RETURN
CALCULATE(MAX(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MaxCount))
Replace "YourTable" with the name of your data table.
Display the Most Frequent Date:
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:
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:
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?
User | Count |
---|---|
122 | |
98 | |
89 | |
74 | |
67 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |