Re: Filter a table with DateTime by a Date slicer

Sachintha · ‎08-25-2022

I have a table with a DateTime column and a few other columns, say, { ID, Unit, and DateTime }. I'm including the raw data at the bottom of the post here.

After importing this CSV to Power BI, I'd like to create a bar chart with Count of ID by Unit, like below, and easily doable.

However, I want to be able to filter this data by different date components such as Year, Month, Work Week, etc. For this, first I created a Date Table. I need a Date Table, as I will have more tables that aren't shown here, and would like to have common filters and such, so I don't want to use the DateTime column in my data table as the filter.

Shortened version of my date table looks like this. I will use CALENDARAUTO() in my actual report, this is just for ease of demonstration. I made sure it's marked as a Date Table, and also the Date Table is linked to my data table properly.

DateTable = ADDCOLUMNS (
    CALENDAR(MIN('MyData'[DateTime]), MAX('MyData'[DateTime])),
    "Year", YEAR ( [Date] ),
    "MonthOfYear", FORMAT ( [Date], "MM" ),
    "WW", YEAR ( [Date] ) & WEEKNUM( [Date] )
)

However, when I do this, and then add a slicer (say, a Work Week slicer) and try to filter my chart, it doesn't as expected. Two things happen here:

The slicer adds a [ Blank ] entry although my Date Table doesn't have blank WW entries.
It shows zero data if I filter using any Work Weeks, and shows full data if I check the [ Blank ] entry (see below).

I figure this is because the my original data table contains DateTime data while in the Date Table, all hh:mm:ss data are 0.

How can I achieve what I want?

Raw data below.

ID,Unit,DateTime
A1,N580,2022-08-01 13:33:06
A2,N580,2022-08-01 13:33:07
A3,N580,2022-08-01 13:33:09
A4,N580,2022-08-01 13:33:09
A5,N580,2022-08-03 09:18:36
A6,N580,2022-08-03 09:26:23
A7,N580,2022-08-03 09:26:23
A8,N580,2022-08-03 13:42:14
A9,N580,2022-08-03 13:42:09
A10,N580,2022-08-03 13:42:11
A11,N580,2022-08-03 13:42:14
A12,N580,2022-08-05 13:51:17
A13,N580,2022-08-03 14:31:36
A14,N580,2022-08-03 18:02:30
A15,N580,2022-08-03 18:02:30
A16,N580,2022-08-03 18:02:30
A17,N580,2022-08-05 13:51:13
A18,N580,2022-08-05 13:51:15
A19,N580,2022-08-05 13:51:17
A20,P403,2022-08-03 23:37:03
A21,P403,2022-08-03 23:37:04
A22,P403,2022-08-03 23:37:05
A23,P403,2022-08-03 23:37:20
A24,P403,2022-08-03 23:51:02
A25,P403,2022-08-04 00:02:34
A26,P406,2022-07-31 00:50:36
A27,P406,2022-07-31 00:51:25
A28,P406,2022-07-31 00:52:24
A29,S408,2022-08-02 11:49:37
A30,S408,2022-07-30 19:39:29
A31,S408,2022-07-30 19:39:32
A32,S408,2022-07-30 23:31:51
A33,S408,2022-07-30 23:31:55
A34,S408,2022-07-31 01:47:59
A35,S408,2022-07-31 01:48:03
A36,S408,2022-07-31 02:48:53
A37,S408,2022-07-31 02:48:57
A38,S408,2022-07-31 03:51:00
A39,S408,2022-07-31 03:51:03
A40,S408,2022-07-31 05:17:36
A41,S408,2022-07-31 05:17:39
A42,S408,2022-07-31 06:37:49
A43,S408,2022-07-31 06:37:53
A44,S408,2022-07-31 07:34:40
A45,S408,2022-07-31 07:34:44
A46,S408,2022-07-31 11:22:03
A47,S408,2022-07-31 11:22:04
A48,S408,2022-07-31 11:35:23
A49,S408,2022-07-31 11:35:23
A50,S408,2022-07-31 11:49:12
A51,S408,2022-07-31 11:49:13
A52,S408,2022-07-31 11:49:34
A53,S408,2022-07-31 11:49:34
A54,S408,2022-07-31 14:41:37
A55,S408,2022-07-31 14:41:41

davehus · ‎08-25-2022

Hi @Sachintha , You can convert the datetime to date in power query and this will solve your problem.

Hope this helps

Did I help you today? Please accept my solution and hit the Kudos button.

Sachintha · ‎08-25-2022

I still need the DateTime to be DateTime, since I need to use the time in other charts in my report.

davehus · ‎08-26-2022

Hi @Sachintha , You could use DateTime.Date(datetimecolumn) in powerquery and extract the date and join to your data table. That way, you retain your datetime for other charts.

Did I help you today? Please accept my solution and hit the Kudos button.