Forum Discussion
Help with Hourly Data Model
I will try to explain my end result :
My summary table doesn't have hourly data. All my reports are based on the summary table, including slicers. However, whenever I have a need to report on hourly data on the same report, I cant. There is no direct connection from the summary table to the original. I have tried all your suggestions above - including creating a reference date table which sits between the two - Got an error message - hence my suggestion to table a look at my sample file to undersand my problem. I will appreciate any help I can get. Been at this for days.
Here is what I think you need.
Step 1
Add another table called Hours with following columns and it will have 24 rows as 24 hours of the day
HourKey, StandardTime
HourKey will be integer unique 0 to 23
STandard Time will be something what you want to show like "1:00:00AM" and so on..
Step 2.
In your data table, extract hour from your date time field, call it HourKey and make it integer
Step 3:
Set relation between your hour table and data table on hour key.
Add slicer from your hour table and this will filter the data by hour.
Now only thing which I'm not clear is if you have Summary Table precalcuated, as this doesn't have link to hour table, you will not able to filter summary table but I'm not sure what is the role of summary table if you already have data table. If you can provide more information, will able to help.
Hi parry2k
Thanks for chiming in. Yes, my summary table is precalculated - which is making things difficult for me. I have a summary table because I haven't figured out how to aggregate the following metrics with DAX formulas over the data table with hourly granularity. I thought summarizing by day in a calculated table makes things easier:
- Total value by day ( 24 hour aggregation )
- Average value by day ( average of aggregated hourly data, over a number of days )
- Max daily value ( Max of the computed daily values over a number of days )
- Max hourly value - Max od hourly data over a number of days
Then of course, there are the higher level roll ups - MTD, QTD, YED etc. I am yet to find any reference to dealing with hourly data rollups or aggregation in Dax.