PROBLEMS WITH DATES AND DATE TABLES

[Anonymous]

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?

[dax · Accepted solution]

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.

[jdbuchanan71]

Hello @Anonymous 

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.