Chronological basis mismatch

[UsePowerBI]

Hello

I have data on two chronological bases:

1) every DateTime at 00:00:00

2) every DateTime per hour

When I graph these, there are issues because the values on the Date basis will only match the values on the DateTime basis when time is 00:00:00.

E.g.

Value1, 1, 01/01/2021 00:00:00

Value1, 2, 01/01/2021 01:00:00

etc...

and only:

Value2, 3, 01/01/2021 00:00:00

Is there an efficient way to fix this mismatch? I was thinking to create records of the data per 00:00:00 for every hour of a Date but this will create a large number of additional records so I wondered if there is any better way?

If not, what would be the best way to generate these hourly records of the values that only have a 00:00:00 value?

Thanks! 

[sevenhills]

I really dont know my reply fits for you.

I understand your post as

a) Fact / Tx records has both date and time

b) You want to track by hourly basis ~ aka in hourly buckets

c) some or majority of records have only time as "00:00:00" ~ kind of considered as no time rows, but recorded for tracking as "00:00:00" in the date time field

My 2 cents

i) It is like Transaction Hour in same or separate dimension concept

ii) Date dimension , Time (Hour) Dimension can be used and in the transaction table as two separate columns.

   i..e, your transaction date is Foreign Key to Date Dimension

         create time dimension containing 1440 rows like one row per minute

                and your transaction time is foreign key to this dimension

         Note: if you want you can create 24 rows like one per hour too.

iii) you are storing and tracking the data as both date and time, as it is.

iv) your reporting uses date as date, but time as hours 

Hope this helps!