Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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?

 

 

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    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.

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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.