Using dates for filtering grouped table

[ezequiel]

Hi,

Sorry for the basic question, but I have a table that has the following fields:

- Transaction Date

- Category

- Subcategory

- Revenue

I need to biuld a view to show the info grouped by Category, subcategory and revenue. And also be able to filter by transaction date.

The solution I found was to group the info from the original table, but on that case I'm removing the Transaction Date to have the info grouped by Category and subcategory, reason why I can't use the date field later for filtering the information.

How can I solve that? What Am I missing? How can I include the date back for using it as s filter on the grouped info?

Thanks,

Ezequiel

[TomMartens]

Hey,

maybe i'm missing some important fact, but why just create a slicer from the columns, whatever you select in the slicer filters / groups the rows in your table.

Maybe you also might consider to create a separate date table and use relationshops in you data model.

Hopefully this is what you are looking for and I'm not overlooking the obvious

Regards

Tom

[ezequiel]

Hi Tom, thanks for the reply. Maybe I'm missing something, but when I create a slicer on the raw data, and apply a filter (let's say February), the data that will be displayed are all the rows and not filtered. That is why I built the grouped info as shown below, but when I did that I lost the date field

Any help will be great

Ezequiel

[TomMartens]

Hm,

what I described above is basically the magic of Power BI, besides some ohter things 🙂

Can you provide a screenshot from the "Relationships" pane something similar like this

or better provide sample data?

Regards

Tom

[ezequiel]

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

[TomMartens]

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