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,
I am trying to calculate average price in a particular time slot
Input data is
Date | Start Time | End Time | A1 |
05-01-2018 | 00:00:00 | 00:15:00 | 1901.93 |
05-01-2018 | 00:15:00 | 00:30:00 | 1901.25 |
05-01-2018 | 00:30:00 | 00:45:00 | 1899.16 |
05-01-2018 | 00:45:00 | 01:00:00 | 1799.95 |
05-01-2018 | 01:00:00 | 01:15:00 | 1799.78 |
05-01-2018 | 01:15:00 | 01:30:00 | 1799.72 |
05-01-2018 | 01:30:00 | 01:45:00 | 1799.6 |
05-01-2018 | 01:45:00 | 02:00:00 | 1799.46 |
05-01-2018 | 02:00:00 | 02:15:00 | 1799.89 |
05-01-2018 | 02:15:00 | 02:30:00 | 1799.87 |
05-01-2018 | 02:30:00 | 02:45:00 | 1799.81 |
05-01-2018 | 02:45:00 | 03:00:00 | 1799.92 |
05-01-2018 | 03:00:00 | 03:15:00 | 1799.76 |
05-01-2018 | 03:15:00 | 03:30:00 | 1799.8 |
05-01-2018 | 03:30:00 | 03:45:00 | 1799.69 |
05-01-2018 | 03:45:00 | 04:00:00 | 1899.79 |
05-01-2018 | 04:00:00 | 04:15:00 | 1901.92 |
05-01-2018 | 04:15:00 | 04:30:00 | 1999.26 |
05-01-2018 | 04:30:00 | 04:45:00 | 1901.91 |
05-01-2018 | 04:45:00 | 05:00:00 | 2109.63 |
05-01-2018 | 05:00:00 | 05:15:00 | 2318.34 |
05-01-2018 | 05:15:00 | 05:30:00 | 2359.23 |
05-01-2018 | 05:30:00 | 05:45:00 | 2406.2 |
05-01-2018 | 05:45:00 | 06:00:00 | 2409.61 |
05-01-2018 | 06:00:00 | 06:15:00 | 2699.32 |
05-01-2018 | 06:15:00 | 06:30:00 | 2699.56 |
05-01-2018 | 06:30:00 | 06:45:00 | 2789.68 |
05-01-2018 | 06:45:00 | 07:00:00 | 2799.38 |
05-01-2018 | 07:00:00 | 07:15:00 | 3000.34 |
05-01-2018 | 07:15:00 | 07:30:00 | 3099.82 |
05-01-2018 | 07:30:00 | 07:45:00 | 3449.95 |
05-01-2018 | 07:45:00 | 08:00:00 | 3999.9 |
05-01-2018 | 08:00:00 | 08:15:00 | 3989.94 |
05-01-2018 | 08:15:00 | 08:30:00 | 4400.53 |
05-01-2018 | 08:30:00 | 08:45:00 | 4600.16 |
05-01-2018 | 08:45:00 | 09:00:00 | 4600.98 |
05-01-2018 | 09:00:00 | 09:15:00 | 5000.72 |
04-01-2018 | 00:00:00 | 00:15:00 | 1999.91 |
04-01-2018 | 00:15:00 | 00:30:00 | 1999.47 |
04-01-2018 | 00:30:00 | 00:45:00 | 1999.2 |
04-01-2018 | 00:45:00 | 01:00:00 | 1901.63 |
04-01-2018 | 01:00:00 | 01:15:00 | 1901.28 |
04-01-2018 | 01:15:00 | 01:30:00 | 1901.36 |
04-01-2018 | 01:30:00 | 01:45:00 | 1901 |
04-01-2018 | 01:45:00 | 02:00:00 | 1800.51 |
04-01-2018 | 02:00:00 | 02:15:00 | 1850.04 |
04-01-2018 | 02:15:00 | 02:30:00 | 1800.48 |
04-01-2018 | 02:30:00 | 02:45:00 | 1800.2 |
04-01-2018 | 02:45:00 | 03:00:00 | 1800.04 |
04-01-2018 | 03:00:00 | 03:15:00 | 1700.8 |
04-01-2018 | 03:15:00 | 03:30:00 | 1901.32 |
04-01-2018 | 03:30:00 | 03:45:00 | 1800.82 |
04-01-2018 | 03:45:00 | 04:00:00 | 1901.59 |
04-01-2018 | 04:00:00 | 04:15:00 | 1901.2 |
04-01-2018 | 04:15:00 | 04:30:00 | 1999.12 |
04-01-2018 | 04:30:00 | 04:45:00 | 1999.93 |
04-01-2018 | 04:45:00 | 05:00:00 | 2139.09 |
04-01-2018 | 05:00:00 | 05:15:00 | 2300.71 |
04-01-2018 | 05:15:00 | 05:30:00 | 2300.9 |
04-01-2018 | 05:30:00 | 05:45:00 | 2369.3 |
04-01-2018 | 05:45:00 | 06:00:00 | 2369.75 |
04-01-2018 | 06:00:00 | 06:15:00 | 2609.66 |
04-01-2018 | 06:15:00 | 06:30:00 | 2599.9 |
04-01-2018 | 06:30:00 | 06:45:00 | 2789.2 |
04-01-2018 | 06:45:00 | 07:00:00 | 2789.63 |
04-01-2018 | 07:00:00 | 07:15:00 | 3000.13 |
04-01-2018 | 07:15:00 | 07:30:00 | 3200.41 |
04-01-2018 | 07:30:00 | 07:45:00 | 3449 |
04-01-2018 | 07:45:00 | 08:00:00 | 3999.97 |
04-01-2018 | 08:00:00 | 08:15:00 | 4213.15 |
04-01-2018 | 08:15:00 | 08:30:00 | 4403.38 |
04-01-2018 | 08:30:00 | 08:45:00 | 4600.54 |
04-01-2018 | 08:45:00 | 09:00:00 | 4530.6 |
04-01-2018 | 09:00:00 | 09:15:00 | 4717.12 |
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 |
03-01-2018 | 04:00:00 | 04:15:00 | 1999.19 |
03-01-2018 | 04:15:00 | 04:30:00 | 1999.52 |
03-01-2018 | 04:30:00 | 04:45:00 | 2166.04 |
03-01-2018 | 04:45:00 | 05:00:00 | 2270.92 |
03-01-2018 | 05:00:00 | 05:15:00 | 2419.84 |
03-01-2018 | 05:15:00 | 05:30:00 | 2469.17 |
03-01-2018 | 05:30:00 | 05:45:00 | 2606.18 |
03-01-2018 | 05:45:00 | 06:00:00 | 2699.01 |
03-01-2018 | 06:00:00 | 06:15:00 | 2732.79 |
03-01-2018 | 06:15:00 | 06:30:00 | 2789.31 |
03-01-2018 | 06:30:00 | 06:45:00 | 2789.31 |
03-01-2018 | 06:45:00 | 07:00:00 | 2799.52 |
03-01-2018 | 07:00:00 | 07:15:00 | 2878.9 |
03-01-2018 | 07:15:00 | 07:30:00 | 2939.88 |
03-01-2018 | 07:30:00 | 07:45:00 | 3100.47 |
03-01-2018 | 07:45:00 | 08:00:00 | 3449.92 |
03-01-2018 | 08:00:00 | 08:15:00 | 3600.03 |
03-01-2018 | 08:15:00 | 08:30:00 | 3700.77 |
03-01-2018 | 08:30:00 | 08:45:00 | 4209.82 |
03-01-2018 | 08:45:00 | 09:00:00 | 4119.99 |
03-01-2018 | 09:00:00 | 09:15:00 | 4396.2 |
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 |
02-01-2018 | 06:15:00 | 06:30:00 | 2679.86 |
02-01-2018 | 06:30:00 | 06:45:00 | 2699.26 |
02-01-2018 | 06:45:00 | 07:00:00 | 2730.19 |
02-01-2018 | 07:00:00 | 07:15:00 | 2716.48 |
02-01-2018 | 07:15:00 | 07:30:00 | 2789.3 |
02-01-2018 | 07:30:00 | 07:45:00 | 2916.14 |
02-01-2018 | 07:45:00 | 08:00:00 | 3449.41 |
02-01-2018 | 08:00:00 | 08:15:00 | 3488.61 |
02-01-2018 | 08:15:00 | 08:30:00 | 3543.95 |
02-01-2018 | 08:30:00 | 08:45:00 | 3543.99 |
02-01-2018 | 08:45:00 | 09:00:00 | 3509.07 |
02-01-2018 | 09:00:00 | 09:15:00 | 4000.48 |
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 |
01-01-2018 | 04:00:00 | 04:15:00 | 1999.18 |
01-01-2018 | 04:15:00 | 04:30:00 | 1999.29 |
01-01-2018 | 04:30:00 | 04:45:00 | 1999.01 |
01-01-2018 | 04:45:00 | 05:00:00 | 1999.6 |
01-01-2018 | 05:00:00 | 05:15:00 | 2166.23 |
01-01-2018 | 05:15:00 | 05:30:00 | 2248.38 |
01-01-2018 | 05:30:00 | 05:45:00 | 2359.16 |
01-01-2018 | 05:45:00 | 06:00:00 | 2388.38 |
01-01-2018 | 06:00:00 | 06:15:00 | 2719.37 |
01-01-2018 | 06:15:00 | 06:30:00 | 2789.05 |
01-01-2018 | 06:30:00 | 06:45:00 | 2789.93 |
01-01-2018 | 06:45:00 | 07:00:00 | 2939.78 |
01-01-2018 | 07:00:00 | 07:15:00 | 2939.95 |
01-01-2018 | 07:15:00 | 07:30:00 | 2939.51 |
01-01-2018 | 07:30:00 | 07:45:00 | 2989.86 |
01-01-2018 | 07:45:00 | 08:00:00 | 3738.58 |
01-01-2018 | 08:00:00 | 08:15:00 | 3499.95 |
01-01-2018 | 08:15:00 | 08:30:00 | 3989.38 |
01-01-2018 | 08:30:00 | 08:45:00 | 4000.5 |
01-01-2018 | 08:45:00 | 09:00:00 | 4099.7 |
01-01-2018 | 09:00:00 | 09:15:00 | 4272.7 |
Looking for output like this
Date | Start Time | End Time | Price |
01-01-2018 | 00:00:00 | 04:00:00 | 1,805.30 |
01-01-2018 | 01:00:00 | 05:00:00 | 1,823.97 |
01-01-2018 | 02:00:00 | 06:00:00 | 1,922.96 |
01-01-2018 | 03:00:00 | 07:00:00 | 2,153.48 |
01-01-2018 | 04:00:00 | 08:00:00 | 2,438.54 |
01-01-2018 | 05:00:00 | 09:00:00 | 2,890.50 |
01-01-2018 | 06:00:00 | 10:00:00 | 3,356.75 |
Try this. File attached as well.
First a calc column to be used as visual filter
Column_for_visual = MINUTE([Start Time])
Now a column for End Time for the final visual
End_Time = [Start Time]+Time(4,0,0)
Now we can use this MEASURE
Price Measure = CALCULATE ( AVERAGE ( Table1[A1] ), FILTER ( ALLEXCEPT ( Table1, Table1[Date] ), [Start Time] >= SELECTEDVALUE ( Table1[Start Time] ) && [End Time] <= ( SELECTEDVALUE ( Table1[Start Time] ) + TIME ( 4, 0, 0 ) ) ) )
I tried the way you suggested the result im getting doesnt have the time 1-5 am as well as 3 to 7 am
Also I have a Slicer on the regions So when I am changing the regions the value does change
The Measure I used is
Measure 2 = CALCULATE(AVERAGE(Append1[Price]),FILTER(Append1,[Start Time]>=SELECTEDVALUE(Append1[Start Time])&&[adj_end]<=SELECTEDVALUE(Append1[Start Time])+TIME(4,0,0)))
I havent used allexcept because I have added a date slicer so using ALLEXCEPT would cancel the effect of the slicer.
The raw table is like this
Visual level is set to 0
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |