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
Jamie_wong
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
brigittagemes
Helper I
Helper I

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
dax
Community Support
Community Support

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.

brigittagemes
Helper I
Helper I

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

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.