Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Sachintha
Helper III
Helper III

Filter a table with DateTime by a Date slicer

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.

CountOfIDByUnit.PNG

 

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] )
)

MyDataDateTime.jpg

 

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:

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

WWFilter1.jpg

 

WWFilter2.jpg

 

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

 

3 REPLIES 3
davehus
Memorable Member
Memorable Member

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.

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

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.