Combine date and time to create a perfect dataset with 24H per day for a periode of two year

[Sannasol]

I have a model that works nicely with a time reference every day.
The problems starts when I want to take advantage of the fact that I have new data points from measurements every 15 minutes.
I would very much like be able to aggregate my data at least on hour basis and not just days in order to see effect of different setting on the machinery on our plant.
I've made a list for 2013 with timesteps every 15 minutes and then I've made a coupling between my measured data, collected at the same timesteps.

I think the reason why it doesn't work is that the two time colomns do not match perfectly numericly due to "sliding seconds due to the numerical representatiojn of data nad time in excell)". Is this correct? I do not get any error message, Power Pivot simply do not aggregate correctly. I guess what could solve my problem ( if I'm righ in my diagnoses) is a combination of dateadd and timeadd.
But there is no Timeadd in DAX.

I'm grateful for all advice.

Sannsol.

 

[RickXL]

It sounds as if you need to group your results into 15 minute slots? If so then read on ...

In Excel, dates are stored as integers and times are fractions of a day. This results in date/time values that can be formatted like this:

2013/10/06 10:45:00


Assuming you have your times in that format then the following formula should group any date/time value in cell A2 into 15 minute buckets. It will round the times back to the previous quarter hour.

=INT(A2)+INT(MOD(A2,1)*96)/96

This should then match your pre-defined groupings.

 

[miguel.escobar]

recommendation: try creating a time table (for hours and minutes) and have a separate table just for the dates...then you'll create 2 calc columns from your main or fact table using the functions DATE (year, month and day) and TIME (hour and minute). This will help you create a pivot table for a whole day. If you need a contiguous pivot table from 1 day to the next then you'll have to make a product of both of those tables to a completely new table either sing SQL statements or with Power Query.