Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
165 | |
86 | |
68 | |
68 | |
58 |