cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EduardoPach Frequent Visitor
Frequent Visitor

PROBLEMS WITH DATES AND DATE TABLES

Hello everybody, I'm having some difficulty with the report I'm creating right now. The problem is that in my data I have the production made in a factory per shift. Example:

Date                               Operator                         Production      Shift

01/01/2019 00:00         Mark                                 120                 A

01/01/2019 08:00         Mark                                 120                 B

01/01/2019 16:00         Mark                                 120                 C

 

Since I have multiple tables I created a date table in order to being able to filter through data in the report relative to the date, however in this date table I have only dates like 01/01/2019 00:00 , because of that in all my visualizations the only data I can filter relative to date is that from Shift A. Any ideas how I could work around this proble?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: PROBLEMS WITH DATES AND DATE TABLES

Hi EduardoPach, 

I am not sure whether you want to create a calendar table which duration is one hour or you want to change datetime to date?

If you want to create datetime calendar table, you could try below M code

let
    Source = List.DateTimes(#datetime(2019, 01, 01, 00, 00, 0), 1000, #duration(0, 1, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

You could refer to List.DateTimes  for details.

If you want to change datetime to date, you could right click column to change type->date in Edit Queries for both calendar and fact table.

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: PROBLEMS WITH DATES AND DATE TABLES

Hello @EduardoPach 

You could either remove the time component from the date when you bring the data into the model or you could add a column that only has the date.  Something like 

Date No Time = DATE ( YEAR ( YourTable[Date] ),  MONTH ( YourTable[Date] ),  DAY ( YourTable[Date] ) )

Then join to your date table on that new field.

Community Support Team
Community Support Team

Re: PROBLEMS WITH DATES AND DATE TABLES

Hi EduardoPach, 

I am not sure whether you want to create a calendar table which duration is one hour or you want to change datetime to date?

If you want to create datetime calendar table, you could try below M code

let
    Source = List.DateTimes(#datetime(2019, 01, 01, 00, 00, 0), 1000, #duration(0, 1, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

You could refer to List.DateTimes  for details.

If you want to change datetime to date, you could right click column to change type->date in Edit Queries for both calendar and fact table.

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors