Forum Discussion

Iva_C_Maia's avatar
Iva_C_Maia
Frequent Visitor
4 years ago
Solved

Calendar Table is not slicing my data correctly

Hi,

Still on my early days in power bi. Appreciate your wise help 🙂

I have a fact table with 2 relevant date columns, that contain both date and time information (year, month, week, day hhmmss).

I need this information (date and time) for a calculated column that shows the time difference between events.

I have created 2 calendar tables, 1 for each of the 2 date columns in my fact table and created the corresponding relationships in my model.

However, when tried to slice my data per year (only need to slice by year, month, day), most of the data gets assigned to blank field as below:

 

I think the problem is related with having time information on my date columns and not on my calendar tables (where I don't need it), but can't seem to find a way to solve it.

Really appreciate all the help you can give me!

Thanks in advance

Iva

 

 

Need Help

5 Replies

  • Iva_C_Maia's avatar
    Iva_C_Maia
    Frequent Visitor
    4 years ago

    Correction:

    when I say "that contain both date and time information (year, month, week, day hhmmss)."

    I mean day, month, year hhmmss - 16/03/2020 11:07:13

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      hi Iva_C_Maia 

      the date format does not seem to be the problem. You have two date tables. What are you trying to achieve exactly? Provide a sample of your data and explain the meaning of each date/column along with final expected results. Thank you

      • Iva_C_Maia's avatar
        Iva_C_Maia
        Frequent Visitor
        4 years ago

        I have a large database of tests (1 ID_test per row), each test has a date/time for event1 (date column1 date) and a date/time for event2 (date column2).

        I have 2 objetives:

        1 - calculate the time difference betwwen event1 and event2 (this I have done, using a calculed column with datediff);

        2 - create visuals where I see the distribution of tests per year, month, day, considering the date when event1 and/or event2 took place.

        I hope this was clear. Thanks you for your help!