Forum Discussion
Transform weekly data into hourly data
So the situation is as follows, I have weekly records of events and i want to make it hourly, logically every hour will have the same value, that corresponds to the whole week, but i need it to be hourly so i can plot it with other hourly data i have. Any ideas on what to do? I tried creating a table having the specified beginning and ending dates i need and every hour of these days, but i am not able to copy the data from the other tble that contains the weekly data, i dont know how to use dax for it, am i missing something? Is there a easy way to do it?
Hi artp3642 ,
I am sorry i couldn't entirely understand what does your weekly record look like and hourly look like?
generate(filter( generate(summarize(Date, date[Date], Date[D WeeK end Date]), Table) , [Week End Date] = [D WeeK end Date]),[Hour Table])
The first Date means a table. It is the first argument of summarize().
Actually, you can create a Date table contains Year,Month,Day, Weeknum,Hour. Then create a relationship between Date table and Fact table, which is one to many and depended on Date/time column.
By this code:
Date = GENERATESERIES(DATE(2022,1,1),DATE(2022,12,31),TIME(1,0,0))Model:Then you can plot with other hourly date.
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
artp3642 , Assume you have data with week end date , some number
create a date table with week end date ,
Hour Table = generateseries(1,24,1)
generate(filter( generate(summarize(Date, date[Date], Date[D WeeK end Date]), Table) , [Week End Date] = [D WeeK end Date]),[Hour Table])
if needed divide the value column by 7*24 to allocate
so first i create a table clalled hour/-table and then create a column using the followeing expression?
generate(filter( generate(summarize(Date, date[Date], Date[D WeeK end Date]), Table) , [Week End Date] = [D WeeK end Date]),[Hour Table])
I didnt understand what Date in the first argument mean, is it a table, or what, and neither Table as the last argument
amitchandak would you please clarify what exactly the formula, it is a bit confusing for me, many thanks in advance
Hi artp3642 ,
I am sorry i couldn't entirely understand what does your weekly record look like and hourly look like?
generate(filter( generate(summarize(Date, date[Date], Date[D WeeK end Date]), Table) , [Week End Date] = [D WeeK end Date]),[Hour Table])
The first Date means a table. It is the first argument of summarize().
Actually, you can create a Date table contains Year,Month,Day, Weeknum,Hour. Then create a relationship between Date table and Fact table, which is one to many and depended on Date/time column.
By this code:
Date = GENERATESERIES(DATE(2022,1,1),DATE(2022,12,31),TIME(1,0,0))Model:Then you can plot with other hourly date.
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
