cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
reije021 Frequent Visitor
Frequent Visitor

Compare totals with last hour

I have a table with hours en total calls per hour

how can I compare the totals with the hour before in a new column

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Compare totals with last hour


@reije021 wrote:
Hour Total Calls total calls hour-1
08:00 34  
09:00 25 34
10:00 44 25
11:00 45 44
12:00 23 45
13:00 34 23
14:00 34 34
15:00 35 34
16:00 44 35
17:00 23 44

 

 

Hi @Interkoubess

Thank you, Yes I can

I need the formula, solution for the totalcalls hour-1 column

Marcel


@reije021

You can create a DAX measure as below.

Measure =
CALCULATE (
    SUM ( Table1[Total Calls] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        TIMEVALUE ( Table1[Hour] )
            = TIMEVALUE ( MAX ( Table1[Hour] ) - 1 / 24 )
    )
)

Capture.PNG

View solution in original post

8 REPLIES 8
Interkoubess Established Member
Established Member

Re: Compare totals with last hour

Hi @reije021,

 

Could you please show us a sample with your desired results and we can try to help.

 

Thank you,

 

Ninter

reije021 Frequent Visitor
Frequent Visitor

Re: Compare totals with last hour

HourTotal Callstotal calls hour-1
08:0034 
09:002534
10:004425
11:004544
12:002345
13:003423
14:003434
15:003534
16:004435
17:002344

 

 

Hi @Interkoubess

Thank you, Yes I can

I need the formula, solution for the totalcalls hour-1 column

Marcel

Interkoubess Established Member
Established Member

Re: Compare totals with last hour

Hi @reije021,

 

In Power Query you can use this code below whete I call my table Data ( and I added index column and then the calculation).

Please let me know if you cannot retrieve the results I can try anonther method:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc27EQAhCEXRXogNUGB/rTD238ayPHaGxOA89brT9TDTINE4aA+nG7AM+tFkmCqGtFlmGNJWvRUMadICMaRps79hZb17tK7Uf2dvxL39Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Hour = _t, #"Total Calls" = _t, #"total calls hour-1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hour", type time}, {"Total Calls", Int64.Type}, {"total calls hour-1", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "CustomTest", each if [Index]=0 then null else #"Added Index"{[Index]-1}[Total Calls]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"total calls hour-1", "Index"})
in
    #"Removed Columns"

 

Ninter

Custom_Test.PNG

reije021 Frequent Visitor
Frequent Visitor

Re: Compare totals with last hour

Hi @Interkoubess

thank you

A little bit complicated for me but I will try or can you post the .pbix

do you think you can build the same in dax to use in a visual?

Marcel

 

Interkoubess Established Member
Established Member

Re: Compare totals with last hour

Hi @reije021,

 

Do you know Power Query?

 

Let me know

 

Thx

 

 

reije021 Frequent Visitor
Frequent Visitor

Re: Compare totals with last hour

hi @Interkoubess

I know Power Query in Excel

Marcel

Moderator Eric_Zhang
Moderator

Re: Compare totals with last hour


@reije021 wrote:
Hour Total Calls total calls hour-1
08:00 34  
09:00 25 34
10:00 44 25
11:00 45 44
12:00 23 45
13:00 34 23
14:00 34 34
15:00 35 34
16:00 44 35
17:00 23 44

 

 

Hi @Interkoubess

Thank you, Yes I can

I need the formula, solution for the totalcalls hour-1 column

Marcel


@reije021

You can create a DAX measure as below.

Measure =
CALCULATE (
    SUM ( Table1[Total Calls] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        TIMEVALUE ( Table1[Hour] )
            = TIMEVALUE ( MAX ( Table1[Hour] ) - 1 / 24 )
    )
)

Capture.PNG

View solution in original post

reije021 Frequent Visitor
Frequent Visitor

Re: Compare totals with last hour

Thank you very much!!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,292)