Reply
Highlighted
Member
Posts: 47
Registered: ‎01-11-2017

averageif on time

Hi,

 

I am trying to calculate average price in a particular time slot

 

Input data is

 

DateStart TimeEnd TimeA1
05-01-201800:00:0000:15:001901.93
05-01-201800:15:0000:30:001901.25
05-01-201800:30:0000:45:001899.16
05-01-201800:45:0001:00:001799.95
05-01-201801:00:0001:15:001799.78
05-01-201801:15:0001:30:001799.72
05-01-201801:30:0001:45:001799.6
05-01-201801:45:0002:00:001799.46
05-01-201802:00:0002:15:001799.89
05-01-201802:15:0002:30:001799.87
05-01-201802:30:0002:45:001799.81
05-01-201802:45:0003:00:001799.92
05-01-201803:00:0003:15:001799.76
05-01-201803:15:0003:30:001799.8
05-01-201803:30:0003:45:001799.69
05-01-201803:45:0004:00:001899.79
05-01-201804:00:0004:15:001901.92
05-01-201804:15:0004:30:001999.26
05-01-201804:30:0004:45:001901.91
05-01-201804:45:0005:00:002109.63
05-01-201805:00:0005:15:002318.34
05-01-201805:15:0005:30:002359.23
05-01-201805:30:0005:45:002406.2
05-01-201805:45:0006:00:002409.61
05-01-201806:00:0006:15:002699.32
05-01-201806:15:0006:30:002699.56
05-01-201806:30:0006:45:002789.68
05-01-201806:45:0007:00:002799.38
05-01-201807:00:0007:15:003000.34
05-01-201807:15:0007:30:003099.82
05-01-201807:30:0007:45:003449.95
05-01-201807:45:0008:00:003999.9
05-01-201808:00:0008:15:003989.94
05-01-201808:15:0008:30:004400.53
05-01-201808:30:0008:45:004600.16
05-01-201808:45:0009:00:004600.98
05-01-201809:00:0009:15:005000.72
04-01-201800:00:0000:15:001999.91
04-01-201800:15:0000:30:001999.47
04-01-201800:30:0000:45:001999.2
04-01-201800:45:0001:00:001901.63
04-01-201801:00:0001:15:001901.28
04-01-201801:15:0001:30:001901.36
04-01-201801:30:0001:45:001901
04-01-201801:45:0002:00:001800.51
04-01-201802:00:0002:15:001850.04
04-01-201802:15:0002:30:001800.48
04-01-201802:30:0002:45:001800.2
04-01-201802:45:0003:00:001800.04
04-01-201803:00:0003:15:001700.8
04-01-201803:15:0003:30:001901.32
04-01-201803:30:0003:45:001800.82
04-01-201803:45:0004:00:001901.59
04-01-201804:00:0004:15:001901.2
04-01-201804:15:0004:30:001999.12
04-01-201804:30:0004:45:001999.93
04-01-201804:45:0005:00:002139.09
04-01-201805:00:0005:15:002300.71
04-01-201805:15:0005:30:002300.9
04-01-201805:30:0005:45:002369.3
04-01-201805:45:0006:00:002369.75
04-01-201806:00:0006:15:002609.66
04-01-201806:15:0006:30:002599.9
04-01-201806:30:0006:45:002789.2
04-01-201806:45:0007:00:002789.63
04-01-201807:00:0007:15:003000.13
04-01-201807:15:0007:30:003200.41
04-01-201807:30:0007:45:003449
04-01-201807:45:0008:00:003999.97
04-01-201808:00:0008:15:004213.15
04-01-201808:15:0008:30:004403.38
04-01-201808:30:0008:45:004600.54
04-01-201808:45:0009:00:004530.6
04-01-201809:00:0009:15:004717.12
03-01-201800:00:0000:15:001999.77
03-01-201800:15:0000:30:001999.33
03-01-201800:30:0000:45:001899.85
03-01-201800:45:0001:00:001899.21
03-01-201801:00:0001:15:001801.93
03-01-201801:15:0001:30:001801.9
03-01-201801:30:0001:45:001801.73
03-01-201801:45:0002:00:001801.48
03-01-201802:00:0002:15:001780.53
03-01-201802:15:0002:30:001700.99
03-01-201802:30:0002:45:001700.78
03-01-201802:45:0003:00:001700.33
03-01-201803:00:0003:15:001698.99
03-01-201803:15:0003:30:001801.19
03-01-201803:30:0003:45:001780.44
03-01-201803:45:0004:00:001800.79
03-01-201804:00:0004:15:001999.19
03-01-201804:15:0004:30:001999.52
03-01-201804:30:0004:45:002166.04
03-01-201804:45:0005:00:002270.92
03-01-201805:00:0005:15:002419.84
03-01-201805:15:0005:30:002469.17
03-01-201805:30:0005:45:002606.18
03-01-201805:45:0006:00:002699.01
03-01-201806:00:0006:15:002732.79
03-01-201806:15:0006:30:002789.31
03-01-201806:30:0006:45:002789.31
03-01-201806:45:0007:00:002799.52
03-01-201807:00:0007:15:002878.9
03-01-201807:15:0007:30:002939.88
03-01-201807:30:0007:45:003100.47
03-01-201807:45:0008:00:003449.92
03-01-201808:00:0008:15:003600.03
03-01-201808:15:0008:30:003700.77
03-01-201808:30:0008:45:004209.82
03-01-201808:45:0009:00:004119.99
03-01-201809:00:0009:15:004396.2
02-01-201800:00:0000:15:001999.69
02-01-201800:15:0000:30:001999.07
02-01-201800:30:0000:45:001801.79
02-01-201800:45:0001:00:001791.81
02-01-201801:00:0001:15:001954.14
02-01-201801:15:0001:30:001801.46
02-01-201801:30:0001:45:001800.81
02-01-201801:45:0002:00:001749.47
02-01-201802:00:0002:15:001793.62
02-01-201802:15:0002:30:001780.16
02-01-201802:30:0002:45:001698.83
02-01-201802:45:0003:00:001698.92
02-01-201803:00:0003:15:001780.75
02-01-201803:15:0003:30:001787.65
02-01-201803:30:0003:45:001999.06
02-01-201803:45:0004:00:001999.15
02-01-201804:00:0004:15:001801.68
02-01-201804:15:0004:30:001999.03
02-01-201804:30:0004:45:001801.45
02-01-201804:45:0005:00:001999.39
02-01-201805:00:0005:15:002180.26
02-01-201805:15:0005:30:002318.63
02-01-201805:30:0005:45:002437.46
02-01-201805:45:0006:00:002402.88
02-01-201806:00:0006:15:002679.67
02-01-201806:15:0006:30:002679.86
02-01-201806:30:0006:45:002699.26
02-01-201806:45:0007:00:002730.19
02-01-201807:00:0007:15:002716.48
02-01-201807:15:0007:30:002789.3
02-01-201807:30:0007:45:002916.14
02-01-201807:45:0008:00:003449.41
02-01-201808:00:0008:15:003488.61
02-01-201808:15:0008:30:003543.95
02-01-201808:30:0008:45:003543.99
02-01-201808:45:0009:00:003509.07
02-01-201809:00:0009:15:004000.48
01-01-201800:00:0000:15:001999.21
01-01-201800:15:0000:30:001899.96
01-01-201800:30:0000:45:001899.51
01-01-201800:45:0001:00:001899.13
01-01-201801:00:0001:15:001819.77
01-01-201801:15:0001:30:001819.32
01-01-201801:30:0001:45:001800.78
01-01-201801:45:0002:00:001749.69
01-01-201802:00:0002:15:001749.57
01-01-201802:15:0002:30:001749.45
01-01-201802:30:0002:45:001749.58
01-01-201802:45:0003:00:001749.83
01-01-201803:00:0003:15:001749.65
01-01-201803:15:0003:30:001749.71
01-01-201803:30:0003:45:001749.71
01-01-201803:45:0004:00:001999.01
01-01-201804:00:0004:15:001999.18
01-01-201804:15:0004:30:001999.29
01-01-201804:30:0004:45:001999.01
01-01-201804:45:0005:00:001999.6
01-01-201805:00:0005:15:002166.23
01-01-201805:15:0005:30:002248.38
01-01-201805:30:0005:45:002359.16
01-01-201805:45:0006:00:002388.38
01-01-201806:00:0006:15:002719.37
01-01-201806:15:0006:30:002789.05
01-01-201806:30:0006:45:002789.93
01-01-201806:45:0007:00:002939.78
01-01-201807:00:0007:15:002939.95
01-01-201807:15:0007:30:002939.51
01-01-201807:30:0007:45:002989.86
01-01-201807:45:0008:00:003738.58
01-01-201808:00:0008:15:003499.95
01-01-201808:15:0008:30:003989.38
01-01-201808:30:0008:45:004000.5
01-01-201808:45:0009:00:004099.7
01-01-201809:00:0009:15:004272.7

 

Looking for output like this

 

DateStart TimeEnd TimePrice
01-01-201800:00:0004:00:00  1,805.30
01-01-201801:00:0005:00:00  1,823.97
01-01-201802:00:0006:00:00  1,922.96
01-01-201803:00:0007:00:00  2,153.48
01-01-201804:00:0008:00:00  2,438.54
01-01-201805:00:0009:00:00  2,890.50
01-01-201806:00:0010:00:00  3,356.75
Super User
Posts: 2,669
Registered: ‎09-27-2017

Re: averageif on time

@baronraghu

 

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 ) )
    )
)

averageif.png

Attachment
Member
Posts: 47
Registered: ‎01-11-2017

Re: averageif on time

@Zubair_Muhammad

 

I tried the way you suggested the result im getting doesnt have the time 1-5 am as well as 3 to 7 am

1.PNG

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

2.PNG

 

Visual level is set to 0

3.PNG