Forum Discussion
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.
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 )
10 Replies
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 )
