Forum Discussion
Dynamic value frequency based on date - is it possible?
I've been working on this for a couple of days now and I'm at the point where I need a reality check.
I want to be able to create a chart that shows the frequency of a column value within a selected date range. It seems like it's possible, but I just can't figure out how to do it.
Take this table for example:
| Date | Color |
| 12/2/20 | red |
| 9/2/21 | blue |
| 1/2/22 | red |
| 1/3/22 | green |
| 2/19/22 | red |
| 2/24/22 | purple |
The report has a slicer for the date. Can I create a chart that dynamically counts the frequency of each color for the date range selected in the slicer?
I want the chart to have the dates on the X-axis and the frequency of each color on the Y-axis. The use case is to be able to see the number of times a color has been used in any selected date range, but only for that date range.
Any help is appreciated.
Anonymous This can work, but you'll get 1 for every date with your sample data. Do you have a DimDate table?
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
You could put for example Month in the Axis, Color in the Legend, and then create a measure:
Frequency of Color = COUNT( Table[Color] )
and put that in Values to display the frequency on the Y axis.
Then use your date slicer to change the date range.
1 Reply
Anonymous This can work, but you'll get 1 for every date with your sample data. Do you have a DimDate table?
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
You could put for example Month in the Axis, Color in the Legend, and then create a measure:
Frequency of Color = COUNT( Table[Color] )
and put that in Values to display the frequency on the Y axis.
Then use your date slicer to change the date range.