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

Summarize with Filter that time of each day is the Max

I have a table of sales increasingly with the datetime.

 

For Example:

Date Time                       Total Sales

2019-11-07 01:00:00      50000

2019-11-07 03:00:00      55000

2019-11-08 07:00:00      59000

2019-11-08 14:00:00      40000

2019-11-09 06:00:00      58000

2019-11-09 10:00:00      70000

 

I want to build a table to summarize like following:

Date Time                       Total Sales

2019-11-07 03:00:00      55000

2019-11-08 14:00:00      40000

2019-11-09 10:00:00      70000

 

Can anyone help?? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
brigittagemes Frequent Visitor
Frequent Visitor

Re: Summarize with Filter that time of each day is the Max

Hi @Jamie_wong !

 

I suggest to create a Date Time table fisrt, for example in PowerQuery, to capture all date and time values.

 

Date Time Table 1.jpg

Connect it to your original Sales table, to the Date Time values.

 

Date Time Table 2.jpg

Try to determine the last hour of every day with DAX:

 
Daily Last Hour = CALCULATE(HOUR(MAX('Sales'[Date Time])),
                                ALLEXCEPT('Date Time Table', 'Date Time Table'[Day]))
 
And show the Total Sales value for that particular hour:
 
Total Sales Measure Daily Last = CALCULATE([Total Sales Measure],
                                    FILTER('Date Time Table',
                                        'Date Time Table'[Hour]=[Daily Last Hour]))
 
And there is your table:
 
 
Yours sincerely,
 
Brigi

View solution in original post

2 REPLIES 2
Highlighted
brigittagemes Frequent Visitor
Frequent Visitor

Re: Summarize with Filter that time of each day is the Max

Hi @Jamie_wong !

 

I suggest to create a Date Time table fisrt, for example in PowerQuery, to capture all date and time values.

 

Date Time Table 1.jpg

Connect it to your original Sales table, to the Date Time values.

 

Date Time Table 2.jpg

Try to determine the last hour of every day with DAX:

 
Daily Last Hour = CALCULATE(HOUR(MAX('Sales'[Date Time])),
                                ALLEXCEPT('Date Time Table', 'Date Time Table'[Day]))
 
And show the Total Sales value for that particular hour:
 
Total Sales Measure Daily Last = CALCULATE([Total Sales Measure],
                                    FILTER('Date Time Table',
                                        'Date Time Table'[Hour]=[Daily Last Hour]))
 
And there is your table:
 
 
Yours sincerely,
 
Brigi

View solution in original post

Community Support
Community Support

Re: Summarize with Filter that time of each day is the Max

Hi Jamie_wong,

You could use below M code to create rank column 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3BDcAgDAPAVaK8i+QUaEhXidh/jSL6IWqx/DzZ7sz9cD4hlkQSlCA3MEozFSOTfFwOrv67RtDgbOekrK5sfo1whb22c4LV6bvXHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date Time                       Total Sales" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Date Time                       Total Sales", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"Date Time                       Total Sales.1", "Date Time                       Total Sales.2"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Split Column by Delimiter", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Date Time                       Total Sales.1", type datetime}, {"Date Time                       Total Sales.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date Time                       Total Sales.1", "Date Time"}, {"Date Time                       Total Sales.2", "Total Sales"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Date Time", "Date Time - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date Time - Copy", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Date Time - Copy"}, {{"all", each _, type table [Date Time=datetime, Total Sales=number, #"Date Time - Copy"=date, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "rank",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date Time", "Total Sales", "rank"}, {"Custom.Date Time", "Custom.Total Sales", "Custom.rank"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date Time - Copy", "all"})
in
    #"Removed Columns"

Then use filter to achieve this goal

492.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors