Total Messages vs. Day & Hour in Power view chart

Question

Table-1 has:
Date                                         Number of Msgs
05-01-2015 12:45:26                      2
06-01-2015 16:48:00                      4
07-01-2015 10:15:25                      2
07-01-2015 11:30:21                      3
07-01-2015 12:30:25                      5
07-01-2015 13:15:00                      7
07-01-2015 13:30:00                      2
07-01-2015 13:45:00                      4
07-01-2015 14:00:00                      3

Now let's say the current system time is 14:05:00 on 07-01-2015

when I select Hour filter, the PowerView chart should display 'Number of Msgs' vs X-Axis (last one hour with 15 minutes interval).

when I select Day filter, the PowerView chart should display 'Number of Msgs' vs X-Axis (last 24 hours with 1 hour interval).

I am able to achieve Week filter, Month filter, Year filter. Struggling to get Day and Hour filters in place as it involves time section of a DateTime column.

[Day filter showing everything at 12:00:00AM] Image at: https://www.dropbox.com/s/uj7r8zugal9vx2n/day%20filter.JPG?dl=0

[for more details: manjunath.hireholi@gmail.com]

Is Table-1 in your example your underlying data table, or a Table you have added to your PowerView report? Would you please post a sample of your data model, so it's easier to understand the relationship between your "Date" column, "Date Selection", and "Type", which can be seen on your screenshot. — GShenanigan, Jan 30 '15 at 13:57

score 0 · Answer 1 · answered Mar 11 '15 at 02:24

Here's a Power Query that will classify the dates into the last hour and last 24 hours. You can in Excel select your table, press CTRL+T to make it a table, then on the Power Query tab select "From Table". Then use the advanced editor to replace the default query with the one below. Just double check the column names and table name. The dates you provided are in the future so the you would replace the DateTime.FixedLocalNow() with the appropriate date. I didn't bucket to 15 minute intervals, but that should be quick to do using the power query date functions that you can read about here:

https://support.office.com/en-us/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819?CorrelationId=7e0cdea1-2b70-45c4-93ae-1037a351d004&ui=en-US&rs=en-US&ad=US

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date                ", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date                ", "Date"}, {"    Number of Msgs", "Number of Msgs"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "IsInLast24Hrs", each if(Duration.TotalHours(DateTime.FixedLocalNow() - [Date]) <= 24 and Duration.TotalHours(DateTime.FixedLocalNow() - [Date]) >=0 ) then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "IsInLastHour", each if(Duration.TotalHours(DateTime.FixedLocalNow() - [Date]) <= 1 and Duration.TotalHours(DateTime.FixedLocalNow() - [Date]) >=0 ) then 1 else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Number of Msgs", Int64.Type}, {"IsInLast24Hrs", Int64.Type}, {"IsInLastHour", Int64.Type}})
in
    #"Changed Type1"

Appreciate your using Power BI.

Lukasz P.

Power BI Team, Microsoft

If you'd like to stay up to date with the Power BI developer story updates you can register (http://solutions.powerbi.com/appsuggestion.html) or follow our blog (http://blogs.msdn.com/b/powerbidev/)

Total Messages vs. Day & Hour in Power view chart

1 Answers1