Forum Discussion
Using dates for filtering grouped table
Sure!. This is the sample of the data. I need to chow the info summarize by Category and subcategory whoing the revenue and being able to filter it by the month the case was created. Let me know if this is clear.
| Case Number | Proactive or Reactive | Status Reason | Category | Sub Category | Created On | Revenue |
| Case 1 | Reactive | Closed (Resolved) | Category1 | Subcategory1 | 29/03/2018 18:15 | $ 450.000,00 |
| Case 2 | Reactive | Closed (Resolved) | Category2 | Subcategory2 | 29/03/2018 14:40 | $ 550.000,00 |
| Case 3 | Reactive | Closed (Resolved) | Category3 | Subcategory4 | 27/03/2018 16:10 | $ 312.000,00 |
| Case 4 | Reactive | Closed (Resolved) | Category3 | Subcategory4 | 26/03/2018 16:09 | $ 209.520,00 |
| Case 5 | Reactive | Closed (Resolved) | Category4 | Subcategory3 | 23/03/2018 16:35 | $ 468.000,00 |
| Case 6 | Reactive | Closed (Resolved) | Category2 | Subcategory2 | 23/03/2018 9:48 | $ 75.000,00 |
Hey,
thanks for providing the sample data, I assume that your data model is just one table w/o any separate Calendar table.
Here you will find a little pbix file, as the following screenshot shows the data gets filtered by the Created On column, due to the fact your sample data did not contain date values for different months I filtered the data using just a single day. For the filtering I used the "Timeline" visual from the marketplace:
Here is a screenshot from the same report where I adjusted one date value from your sample data to be in a different month, now I have 2 months to filter and it still works:
Nevertheless, can you please check if your revenue column is a numerical datatype - in no way it has to be "Text".
Mark the column, and check the datatype:
I had to adjust the values in the revenue column, because it has been inserted into PowerBI as text, due to spaces between the currency symbol and the value. If everything is fine you can recognize that the column can be aggregated by the Sigma sign in front of the column name:
You have to change the datatype of the revenue column to a datatype that can be aggregated, but this would be another question and an easy task for Power Query if necessary.
There is one thing that catched my eye, the column Created On also has a time information. In most of the cases I create a new column in my table w/o the time part and then I use this column to create a relationship with a separate Calendar table.
Wondering if this will help, currently I have no idea why the Power BI magic will not work in your case, or I still do not understand your requirement.
Regards
Tom