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.
Hours Online | Date | Location | Days Online | Production |
0 | 1/1/2017 | A | 0 | 0 |
0 | 1/2/2017 | A | 0 | 0 |
12 | 1/3/2017 | A | 1 | 5 |
24 | 1/4/2017 | A | 2 | 10 |
24 | 1/5/2017 | A | 3 | 5 |
24 | 1/6/2017 | A | 4 | 20 |
0 | 1/8/2017 | B | 0 | 0 |
0 | 1/9/2017 | B | 0 | 0 |
0 | 1/10/2017 | B | 0 | 0 |
24 | 1/11/2017 | B | 1 | 5 |
24 | 1/12/2017 | B | 2 | 5 |
5 | 1/13/2017 | B | 2 | 5 |
10 | 1/14/2017 | B | 2 | 10 |
24 | 1/15/2017 | B | 3 | 20 |
The first 3 columns is what i have, and the 4th column is what i would like to calculate.
Basically, i want to convert the 'hours online' values into a 'days online'. im trying to get a calculation that will sum all the hours, and only add 1 day to the to the 'days online' value if the sum is 24.
I am trying to plot 'Days online' on the x axis, and 'Production' on the Y-axis - for both locations, so that i can compare how each location performs relative to each other.
Solved! Go to Solution.
HI @aar0n
Try this Calculated Column
Column = VAR myDaysOnline = CALCULATE ( COUNTROWS ( TableName ), FILTER ( ALLEXCEPT ( TableName, TableName[Location] ), TableName[Hours Online] = 24 && TableName[Date] <= EARLIER ( TableName[Date] ) ) ) RETURN IF ( ISBLANK ( myDaysOnline ), 0, myDaysOnline )
HI @aar0n
In that case use this Column
Column = VAR CumulativeHours = CALCULATE ( SUM ( TableName[Hours Online] ), FILTER ( ALLEXCEPT ( TableName, TableName[Location] ), TableName[Date] <= EARLIER ( TableName[Date] ) ) ) RETURN ROUNDDOWN ( DIVIDE ( CumulativeHours, 24 ), 0 )
HI @aar0n
Please could you try this
Just Replaced ROUNDDown with QUOTIENT
Column = VAR CumulativeHours = CALCULATE ( SUM ( TableName[Hours Online] ), FILTER ( ALLEXCEPT ( TableName, TableName[Location] ), TableName[Date] <= EARLIER ( TableName[Date] ) ) ) RETURN QUOTIENT ( CumulativeHours, 24 )
Hi @aar0n,
I'm still not get your calculation logic, can you provide more details information about this?
At first 3 rows, total hours is twelve, why did you log it as one day? (as you said, if sum 24 hours then you will log it as one day)
Or the 'days online' is a stand alone column who already used to store values?
Regards,
Xiaoxin Sheng
that was a typo - it should say 24 hours instead of 12!! sorry for that, i missed it.
HI @aar0n
Try this Calculated Column
Column = VAR myDaysOnline = CALCULATE ( COUNTROWS ( TableName ), FILTER ( ALLEXCEPT ( TableName, TableName[Location] ), TableName[Hours Online] = 24 && TableName[Date] <= EARLIER ( TableName[Date] ) ) ) RETURN IF ( ISBLANK ( myDaysOnline ), 0, myDaysOnline )
This works Perfect! thank you so much!!
my only question now is, is there a way to make the formula say "when the sum of 'hours online' is 24, then add 1 day"
because i have a dataset of ~50k rows, and just ignoring the values less than 24 would skew the data eventually
so for example,
Hours Online | Date | Location | Days Online |
5 | 1/1/2017 | C | 0 |
19 | 1/2/2017 | C | 1 |
10 | 1/3/2017 | C | 1 |
14 | 1/4/2017 | C | 2 |
24 | 1/5/2017 | C | 3 |
HI @aar0n
In that case use this Column
Column = VAR CumulativeHours = CALCULATE ( SUM ( TableName[Hours Online] ), FILTER ( ALLEXCEPT ( TableName, TableName[Location] ), TableName[Date] <= EARLIER ( TableName[Date] ) ) ) RETURN ROUNDDOWN ( DIVIDE ( CumulativeHours, 24 ), 0 )
Hi,
For some names, i am getting strange instances where the calculation skips some numbers, it especially happens after a value in 'hours online' isnt exactly 24 before the calculation. below is an example, with the missing areas in red
Original Days Online Formula | NEWEST Days Online | Hours Online | Location | Date |
0 | 0 | 0 | D | 2/24/2017 |
0 | 0 | 0 | D | 2/25/2017 |
0 | 0 | 14.1 | D | 2/26/2017 |
1 | 1 | 24 | D | 2/27/2017 |
3 | 3 | 24 | D | 2/28/2017 |
4 | 4 | 24 | D | 3/1/2017 |
5 | 5 | 24 | D | 3/2/2017 |
6 | 6 | 24 | D | 3/3/2017 |
7 | 7 | 24 | D | 3/4/2017 |
7 | 8 | 10.4 | D | 3/5/2017 |
8 | 9 | 24 | D | 3/6/2017 |
9 | 10 | 24 | D | 3/7/2017 |
10 | 11 | 24 | D | 3/8/2017 |
11 | 12 | 24 | D | 3/9/2017 |
12 | 13 | 24 | D | 3/10/2017 |
13 | 14 | 24 | D | 3/11/2017 |
14 | 15 | 24 | D | 3/12/2017 |
15 | 16 | 24 | D | 3/13/2017 |
16 | 17 | 24 | D | 3/14/2017 |
17 | 18 | 24 | D | 3/15/2017 |
18 | 19 | 24 | D | 3/16/2017 |
19 | 20 | 24 | D | 3/17/2017 |
20 | 21 | 24 | D | 3/18/2017 |
21 | 22 | 24 | D | 3/19/2017 |
22 | 23 | 24 | D | 3/20/2017 |
23 | 24 | 24 | D | 3/21/2017 |
24 | 25 | 24 | D | 3/22/2017 |
25 | 26 | 24 | D | 3/23/2017 |
26 | 27 | 24 | D | 3/24/2017 |
27 | 28 | 24 | D | 3/25/2017 |
28 | 29 | 24 | D | 3/26/2017 |
29 | 30 | 24 | D | 3/27/2017 |
29 | 31 | 13.3 | D | 3/28/2017 |
30 | 32 | 24 | D | 3/31/2017 |
30 | 34 | 22.9 | D | 4/1/2017 |
31 | 35 | 24 | D | 4/2/2017 |
32 | 36 | 24 | D | 4/3/2017 |
33 | 37 | 24 | D | 4/4/2017 |
SHORTENED…. EVERYTHING IN THIS SECTION WAS OK…. | ||||
102 | 106 | 24 | D | 6/10/2017 |
103 | 107 | 24 | D | 6/11/2017 |
105 | 109 | 24 | D | 6/12/2017 |
105 | 109 | 21.4 | D | 6/13/2017 |
106 | 110 | 24 | D | 6/14/2017 |
107 | 111 | 24 | D | 6/15/2017 |
108 | 112 | 24 | D | 6/16/2017 |
109 | 113 | 24 | D | 6/17/2017 |
110 | 114 | 24 | D | 6/18/2017 |
111 | 115 | 24 | D | 6/19/2017 |
112 | 116 | 24 | D | 6/20/2017 |
113 | 117 | 24 | D | 6/21/2017 |
113 | 118 | 18.53 | D | 6/22/2017 |
113 | 119 | 16.77 | D | 6/23/2017 |
114 | 120 | 24 | D | 6/24/2017 |
115 | 121 | 24 | D | 6/25/2017 |
116 | 122 | 24 | D | 6/26/2017 |
117 | 123 | 24 | D | 6/27/2017 |
118 | 124 | 24 | D | 6/28/2017 |
119 | 125 | 24 | D | 6/29/2017 |
120 | 126 | 24 | D | 6/30/2017 |
121 | 127 | 24 | D | 7/1/2017 |
122 | 128 | 24 | D | 7/2/2017 |
123 | 129 | 24 | D | 7/3/2017 |
124 | 130 | 24 | D | 7/4/2017 |
125 | 131 | 24 | D | 7/5/2017 |
126 | 132 | 24 | D | 7/6/2017 |
127 | 133 | 24 | D | 7/7/2017 |
128 | 134 | 24 | D | 7/8/2017 |
129 | 135 | 24 | D | 7/9/2017 |
130 | 136 | 24 | D | 7/10/2017 |
130 | 137 | 23 | D | 7/11/2017 |
132 | 139 | 24 | D | 7/12/2017 |
133 | 140 | 24 | D | 7/13/2017 |
134 | 141 | 24 | D | 7/14/2017 |
135 | 142 | 24 | D | 7/15/2017 |
136 | 143 | 24 | D | 7/16/2017 |
137 | 144 | 24 | D | 7/17/2017 |
138 | 145 | 24 | D | 7/18/2017 |
139 | 146 | 24 | D | 7/19/2017 |
140 | 147 | 24 | D | 7/20/2017 |
141 | 148 | 24 | D | 7/21/2017 |
142 | 149 | 24 | D | 7/22/2017 |
143 | 150 | 24 | D | 7/23/2017 |
144 | 151 | 24 | D | 7/24/2017 |
145 | 152 | 24 | D | 7/25/2017 |
146 | 153 | 24 | D | 7/26/2017 |
147 | 154 | 24 | D | 7/27/2017 |
148 | 155 | 24 | D | 7/28/2017 |
149 | 156 | 24 | D | 7/29/2017 |
149 | 156 | 14.8 | D | 7/30/2017 |
149 | 156 | 0 | D | 7/31/2017 |
149 | 157 | 10.2 | D | 8/1/2017 |
151 | 159 | 24 | D | 8/2/2017 |
there are some names in the dataset, where the calculation works great, but there are a handful where it skips some numbers, just like above
HI @aar0n
Please could you try this
Just Replaced ROUNDDown with QUOTIENT
Column = VAR CumulativeHours = CALCULATE ( SUM ( TableName[Hours Online] ), FILTER ( ALLEXCEPT ( TableName, TableName[Location] ), TableName[Date] <= EARLIER ( TableName[Date] ) ) ) RETURN QUOTIENT ( CumulativeHours, 24 )
My newest question:
how would i edit this formula so that the counter works as is - until 1 shows up - then counts just the number of days.
for example,
Hours Online | Date | Location | Days Online | Production |
0 | 1/1/2017 | A | 0 | 0 |
0 | 1/2/2017 | A | 0 | 0 |
12 | 1/3/2017 | A | 1 | 5 |
24 | 1/4/2017 | A | 2 | 10 |
0 | 1/5/2017 | A | 3 | 5 |
24 | 1/6/2017 | A | 4 | 20 |
0 | 1/8/2017 | B | 0 | 0 |
0 | 1/9/2017 | B | 0 | 0 |
0 | 1/10/2017 | B | 0 | 0 |
24 | 1/11/2017 | B | 1 | 5 |
24 | 1/12/2017 | B | 2 | 5 |
5 | 1/13/2017 | B | 3 | 5 |
19 | 1/14/2017 | B | 4 | 10 |
0 | 1/15/2017 | B | 5 | 0 |
24 | 1/16/2017 | B | 6 | 20 |
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |