Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Thank you, Yes I can
I need the formula, solution for the totalcalls hour-1 column
Marcel
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 ) ) )
Hi @reije021,
Could you please show us a sample with your desired results and we can try to help.
Thank you,
Ninter
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 |
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
Thank you, Yes I can
I need the formula, solution for the totalcalls hour-1 column
Marcel
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 ) ) )
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |