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
reije021
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


@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
Solution Sage
Solution Sage

Hi @reije021,

 

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

 

Thank you,

 

Ninter

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


@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

Thank you very much!!

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

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

 

Hi @reije021,

 

Do you know Power Query?

 

Let me know

 

Thx

 

 

hi @Interkoubess

I know Power Query in Excel

Marcel

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.