Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have following data Column 1 denotes Date, Column 2 start time Column 3 End Time Column 4 Price
01-01-2018 | 22:45:00 | 23:00:00 | 1999.69 |
01-01-2018 | 23:00:00 | 23:15:00 | 1999.79 |
01-01-2018 | 23:15:00 | 23:30:00 | 1899.72 |
01-01-2018 | 23:30:00 | 23:45:00 | 1894.18 |
01-01-2018 | 23:45:00 | 00:00:00 | 1749.99 |
02-01-2018 | 00:00:00 | 00:15:00 | 1999.69 |
02-01-2018 | 00:15:00 | 00:30:00 | 1999.07 |
02-01-2018 | 00:30:00 | 00:45:00 | 1801.79 |
02-01-2018 | 00:45:00 | 01:00:00 | 1791.81 |
02-01-2018 | 01:00:00 | 01:15:00 | 1954.14 |
02-01-2018 | 01:15:00 | 01:30:00 | 1801.46 |
02-01-2018 | 01:30:00 | 01:45:00 | 1800.81 |
02-01-2018 | 01:45:00 | 02:00:00 | 1749.47 |
02-01-2018 | 02:00:00 | 02:15:00 | 1793.62 |
02-01-2018 | 02:15:00 | 02:30:00 | 1780.16 |
02-01-2018 | 02:30:00 | 02:45:00 | 1698.83 |
02-01-2018 | 02:45:00 | 03:00:00 | 1698.92 |
02-01-2018 | 03:00:00 | 03:15:00 | 1780.75 |
02-01-2018 | 03:15:00 | 03:30:00 | 1787.65 |
02-01-2018 | 03:30:00 | 03:45:00 | 1999.06 |
02-01-2018 | 03:45:00 | 04:00:00 | 1999.15 |
02-01-2018 | 04:00:00 | 04:15:00 | 1801.68 |
02-01-2018 | 04:15:00 | 04:30:00 | 1999.03 |
02-01-2018 | 04:30:00 | 04:45:00 | 1801.45 |
02-01-2018 | 04:45:00 | 05:00:00 | 1999.39 |
02-01-2018 | 05:00:00 | 05:15:00 | 2180.26 |
02-01-2018 | 05:15:00 | 05:30:00 | 2318.63 |
02-01-2018 | 05:30:00 | 05:45:00 | 2437.46 |
02-01-2018 | 05:45:00 | 06:00:00 | 2402.88 |
02-01-2018 | 06:00:00 | 06:15:00 | 2679.67 |
I am looking to calculate the following
1) Average rolling price per 4 hour spread per month as shown below
start time | end time | January |
00:00:00 | 04:00:00 | 1938.8 |
01:00:00 | 05:00:00 | 2005 |
Solved! Go to Solution.
Hi @baronraghu,
You can refer to following calculate table formula to get a summarize table base on sample data table:
Summary = ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'Sample', MINUTE ( [StartTime] ) = 0 && SECOND ( [StartTime] ) = 0 ), [Date], [StartTime], "End", CALCULATE ( MAX ( 'Sample'[EndTime] ), ALLEXCEPT ( 'Sample', 'Sample'[Date] ) ) ), "AVG", AVERAGEX ( FILTER ( ALL ( 'Sample' ), 'Sample'[Date] = EARLIER ( [Date] ) && 'Sample'[StartTime] >= EARLIER ( [StartTime] ) && 'Sample'[EndTime] <= EARLIER ( [End] ) ), [Value] ) )
Regards,
Xiaoxin Sheng
Hi @baronraghu,
You can refer to following steps to create a table visual to display range average data.
Steps:
1. Create a calculate table with hour and range.
HourList = SELECTCOLUMNS(GENERATESERIES(0,23,1),"Hour",[Value],"Range",INT([Value]/4)+1)
2. Add calculated column to original table to extract hour from start time.
StartHour = HOUR([Start])
3. Create table visual.
Regards,
Xiaoxin Sheng
Hi,
I think the below table will be helpful
01-01-2018 | 00:00:00 | 00:15:00 | 1999.21 |
01-01-2018 | 00:15:00 | 00:30:00 | 1899.96 |
01-01-2018 | 00:30:00 | 00:45:00 | 1899.51 |
01-01-2018 | 00:45:00 | 01:00:00 | 1899.13 |
01-01-2018 | 01:00:00 | 01:15:00 | 1819.77 |
01-01-2018 | 01:15:00 | 01:30:00 | 1819.32 |
01-01-2018 | 01:30:00 | 01:45:00 | 1800.78 |
01-01-2018 | 01:45:00 | 02:00:00 | 1749.69 |
01-01-2018 | 02:00:00 | 02:15:00 | 1749.57 |
01-01-2018 | 02:15:00 | 02:30:00 | 1749.45 |
01-01-2018 | 02:30:00 | 02:45:00 | 1749.58 |
01-01-2018 | 02:45:00 | 03:00:00 | 1749.83 |
01-01-2018 | 03:00:00 | 03:15:00 | 1749.65 |
01-01-2018 | 03:15:00 | 03:30:00 | 1749.71 |
01-01-2018 | 03:30:00 | 03:45:00 | 1749.71 |
01-01-2018 | 03:45:00 | 04:00:00 | 1999.01 |
02-01-2018 | 00:00:00 | 00:15:00 | 1999.69 |
02-01-2018 | 00:15:00 | 00:30:00 | 1999.07 |
02-01-2018 | 00:30:00 | 00:45:00 | 1801.79 |
02-01-2018 | 00:45:00 | 01:00:00 | 1791.81 |
02-01-2018 | 01:00:00 | 01:15:00 | 1954.14 |
02-01-2018 | 01:15:00 | 01:30:00 | 1801.46 |
02-01-2018 | 01:30:00 | 01:45:00 | 1800.81 |
02-01-2018 | 01:45:00 | 02:00:00 | 1749.47 |
02-01-2018 | 02:00:00 | 02:15:00 | 1793.62 |
02-01-2018 | 02:15:00 | 02:30:00 | 1780.16 |
02-01-2018 | 02:30:00 | 02:45:00 | 1698.83 |
02-01-2018 | 02:45:00 | 03:00:00 | 1698.92 |
02-01-2018 | 03:00:00 | 03:15:00 | 1780.75 |
02-01-2018 | 03:15:00 | 03:30:00 | 1787.65 |
02-01-2018 | 03:30:00 | 03:45:00 | 1999.06 |
02-01-2018 | 03:45:00 | 04:00:00 | 1999.15 |
03-01-2018 | 00:00:00 | 00:15:00 | 1999.77 |
03-01-2018 | 00:15:00 | 00:30:00 | 1999.33 |
03-01-2018 | 00:30:00 | 00:45:00 | 1899.85 |
03-01-2018 | 00:45:00 | 01:00:00 | 1899.21 |
03-01-2018 | 01:00:00 | 01:15:00 | 1801.93 |
03-01-2018 | 01:15:00 | 01:30:00 | 1801.9 |
03-01-2018 | 01:30:00 | 01:45:00 | 1801.73 |
03-01-2018 | 01:45:00 | 02:00:00 | 1801.48 |
03-01-2018 | 02:00:00 | 02:15:00 | 1780.53 |
03-01-2018 | 02:15:00 | 02:30:00 | 1700.99 |
03-01-2018 | 02:30:00 | 02:45:00 | 1700.78 |
03-01-2018 | 02:45:00 | 03:00:00 | 1700.33 |
03-01-2018 | 03:00:00 | 03:15:00 | 1698.99 |
03-01-2018 | 03:15:00 | 03:30:00 | 1801.19 |
03-01-2018 | 03:30:00 | 03:45:00 | 1780.44 |
03-01-2018 | 03:45:00 | 04:00:00 | 1800.79 |
What I would like is a below table sumarizing the average price as
Date | Start Time | End Time | Aver Price |
01-01-2018 | 00:00:00 | 04:00:00 | 1938 |
01-01-2018 | 01:00:00 | 04:00:00 | 1863 |
01-01-2018 | 02:00:00 | 04:00:00 | 786 |
02-01-2018 | 00:00:00 | 04:00:00 | 2589 |
02-01-2018 | 01:00:00 | 04:00:00 | 1587 |
02-01-2018 | 02:00:00 | 04:00:00 | 1532 |
03-01-2018 | 00:00:00 | 04:00:00 | 1486 |
03-01-2018 | 01:00:00 | 04:00:00 | 1436 |
03-01-2018 | 02:00:00 | 04:00:00 | 1428 |
In lieu with my previous post I have created a new bucket table like below
Adj_Start Ad_End
00:00:00 | 04:00:00 |
01:00:00 | 04:00:00 |
02:00:00 | 04:00:00 |
I created a dax formula like this
Measure = CALCULATE([average price per day],FILTER(Append1,Append1[Start Time]>=VALUES(bucket[Adj_Start])&&Append1[Start Time]<VALUES(bucket[Ad_End])))
This doesnt seem to work
Hi @baronraghu,
You can refer to following calculate table formula to get a summarize table base on sample data table:
Summary = ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'Sample', MINUTE ( [StartTime] ) = 0 && SECOND ( [StartTime] ) = 0 ), [Date], [StartTime], "End", CALCULATE ( MAX ( 'Sample'[EndTime] ), ALLEXCEPT ( 'Sample', 'Sample'[Date] ) ) ), "AVG", AVERAGEX ( FILTER ( ALL ( 'Sample' ), 'Sample'[Date] = EARLIER ( [Date] ) && 'Sample'[StartTime] >= EARLIER ( [StartTime] ) && 'Sample'[EndTime] <= EARLIER ( [End] ) ), [Value] ) )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |