Compare totals with last hour

[reije021]

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

[Interkoubess]

Hi @reije021,

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

Thank you,

Ninter

[reije021]

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

[Eric_Zhang · Accepted solution]

---

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

[reije021]

Thank you very much!!

[Interkoubess]

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

[reije021]

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]

Hi @reije021,

Do you know Power Query?

Let me know

Thx

[reije021]

hi @Interkoubess

I know Power Query in Excel

Marcel