topic Transform weekly data into hourly data in DAX Commands and Tips

Transform weekly data into hourly data

artp3642 — Thu, 24 Feb 2022 15:06:16 GMT

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?

Re: Transform weekly data into hourly data

amitchandak — Thu, 24 Feb 2022 17:02:51 GMT

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

Re: Transform weekly data into hourly data

artp3642 — Thu, 24 Feb 2022 20:20:36 GMT

amitchandak

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

Re: Transform weekly data into hourly data

artp3642 — Thu, 24 Feb 2022 20:28:48 GMT

amitchandak would you please clarify what exactly the formula, it is a bit confusing for me, many thanks in advance

Re: Transform weekly data into hourly data

v-chenwuz-msft — Tue, 01 Mar 2022 03:11:48 GMT

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.