Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aar0n
Advocate II
Advocate II

How to make a calculated 'days' column

Hours OnlineDateLocationDays OnlineProduction
01/1/2017A00
01/2/2017A00
121/3/2017A15
241/4/2017A210
241/5/2017A35
241/6/2017A420
01/8/2017B00
01/9/2017B00
01/10/2017B00
241/11/2017B15
241/12/2017B25
51/13/2017B25
101/14/2017B210
241/15/2017B320

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

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
v-shex-msft
Community Support
Community Support

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)

10.PNG


Or the 'days online' is a stand alone column who already used to store values?

 

Regards,

Xiaoxin Sheng

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

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 )

Regards
Zubair

Please try my custom visuals

@aar0n

 

 

1048.png


Regards
Zubair

Please try my custom visuals

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 DateLocationDays Online
51/1/2017C0
191/2/2017C1
101/3/2017C1
141/4/2017C2
241/5/2017C3

 

 

 

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 )

Regards
Zubair

Please try my custom visuals

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 FormulaNEWEST Days OnlineHours OnlineLocationDate
000D2/24/2017
000D2/25/2017
0014.1D2/26/2017
1124D2/27/2017
3324D2/28/2017
4424D3/1/2017
5524D3/2/2017
6624D3/3/2017
7724D3/4/2017
7810.4D3/5/2017
8924D3/6/2017
91024D3/7/2017
101124D3/8/2017
111224D3/9/2017
121324D3/10/2017
131424D3/11/2017
141524D3/12/2017
151624D3/13/2017
161724D3/14/2017
171824D3/15/2017
181924D3/16/2017
192024D3/17/2017
202124D3/18/2017
212224D3/19/2017
222324D3/20/2017
232424D3/21/2017
242524D3/22/2017
252624D3/23/2017
262724D3/24/2017
272824D3/25/2017
282924D3/26/2017
293024D3/27/2017
293113.3D3/28/2017
303224D3/31/2017
303422.9D4/1/2017
313524D4/2/2017
323624D4/3/2017
333724D4/4/2017
SHORTENED…. EVERYTHING IN THIS SECTION WAS OK….
10210624D6/10/2017
10310724D6/11/2017
10510924D6/12/2017
10510921.4D6/13/2017
10611024D6/14/2017
10711124D6/15/2017
10811224D6/16/2017
10911324D6/17/2017
11011424D6/18/2017
11111524D6/19/2017
11211624D6/20/2017
11311724D6/21/2017
11311818.53D6/22/2017
11311916.77D6/23/2017
11412024D6/24/2017
11512124D6/25/2017
11612224D6/26/2017
11712324D6/27/2017
11812424D6/28/2017
11912524D6/29/2017
12012624D6/30/2017
12112724D7/1/2017
12212824D7/2/2017
12312924D7/3/2017
12413024D7/4/2017
12513124D7/5/2017
12613224D7/6/2017
12713324D7/7/2017
12813424D7/8/2017
12913524D7/9/2017
13013624D7/10/2017
13013723D7/11/2017
13213924D7/12/2017
13314024D7/13/2017
13414124D7/14/2017
13514224D7/15/2017
13614324D7/16/2017
13714424D7/17/2017
13814524D7/18/2017
13914624D7/19/2017
14014724D7/20/2017
14114824D7/21/2017
14214924D7/22/2017
14315024D7/23/2017
14415124D7/24/2017
14515224D7/25/2017
14615324D7/26/2017
14715424D7/27/2017
14815524D7/28/2017
14915624D7/29/2017
14915614.8D7/30/2017
1491560D7/31/2017
14915710.2D8/1/2017
15115924D8/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 )

Regards
Zubair

Please try my custom visuals

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 OnlineDateLocationDays OnlineProduction
01/1/2017A00
01/2/2017A00
121/3/2017A15
241/4/2017A210
01/5/2017A35
241/6/2017A420
01/8/2017B00
01/9/2017B00
01/10/2017B00
241/11/2017B15
241/12/2017B25
51/13/2017B35
191/14/2017B410
01/15/2017B50
241/16/2017B620

@aar0n

 

1057.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.