cancel
Showing results for
Did you mean:
Member

How to make a calculated 'days' column

 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.

4 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User

Re: How to make a calculated 'days' column

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 )```
Super User

Super User

Re: How to make a calculated 'days' column

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 )```
Super User

Re: How to make a calculated 'days' column

HI @aar0n

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 )```
10 REPLIES 10
Community Support Team

Re: How to make a calculated 'days' column

Hi @aar0n,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

Re: How to make a calculated 'days' column

that was a typo - it should say 24 hours instead of 12!! sorry for that, i missed it.

Highlighted
Super User

Re: How to make a calculated 'days' column

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 )```
Super User

Member

Re: How to make a calculated 'days' column

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

Super User

Re: How to make a calculated 'days' column

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 )```
Super User

Member

Re: How to make a calculated 'days' column

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

Super User

Re: How to make a calculated 'days' column

HI @aar0n

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