Calendar Table is not slicing my data correctly

[Iva_C_Maia]

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

[Iva_C_Maia]

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]

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]

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!

[Iva_C_Maia · Accepted solution]

Hi,

Just to share how I solved the problem with my calendar table not filtering my data per date corretly. It was simple. I copy below the link where I found the solution.

https://radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model

Thanks

[Anonymous]

Hi @Iva_C_Maia ,

Glad to hear you have solved your problem. Please mark the reply as a solution. More people will benefit from it.

Best Regards,

Stephen Tao

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