Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
geeklarokcmie
Helper I
Helper I

Calculate number of hours and minutes spent

This is a two part question.

 

Firstly - what is the forumla or the calculation that can produce number of minutes,hours spent on an email with the same subject?

Secondly - how can I add a note/remarks on a visual bar that can be shown when the specific month is selected (ex: Jan) and hidden for the next month data (ex:Feb)?

SubjectFolderPathDate Received Time ReceivedDate SentTime Sent
test layersinbox\lyc01/24/202410:11:56 AM01/24/202410:11:55 AM
test layersinobx\sent01/24/202410:07:00 AM01/24/202410:07:03 AM
test layersinbox\coa01/26/202411:53:14 AM01/26/202411:53:17 AM
source codeinbox\coa02/19/202412:53:14 PM02/20/202412:53:17 PM
assignment functionalinbox\02/01/20244:53:14 PM02/19/20244:53:17 PM
source codeinbox\sent02/20/20242:53:14 PM02/20/20242:53:17 PM
11 REPLIES 11
v-heq-msft
Community Support
Community Support

Hi @geeklarokcmie ,
This is accomplished by using the MEASURE approach to your request

Here is my test data

vheqmsft_0-1708667358077.png

Create two measures about hour and minute

Duration_hour = 
VAR First_datetime = 
    CALCULATE(
        MIN('Table'[Received]),
        ALLEXCEPT(
            'Table',
            'Table'[Subject]
        )
    )
VAR Last_datetime = 
    CALCULATE(
        MAX('Table'[Sent]),
        ALLEXCEPT(
            'Table',
            'Table'[Subject])
    )
RETURN
DATEDIFF(First_datetime,Last_datetime,HOUR)
Duration_minute = 
VAR First_datetime = 
    CALCULATE(
        MIN('Table'[Received]),
        ALLEXCEPT(
            'Table',
            'Table'[Subject]
        )
    )
VAR Last_datetime = 
    CALCULATE(
        MAX('Table'[Sent]),
        ALLEXCEPT(
            'Table',
            'Table'[Subject])
    )
RETURN
DATEDIFF(First_datetime,Last_datetime,MINUTE)

Final output

vheqmsft_1-1708667459669.png

 

Best regards,

Albert He

 

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

Appreciate providing two measures for my query @v-heq-msft.

 

However, I'm looking to get the amount of hours/minutes spent when the email has same subject title.
How is that achievable?

Hi @geeklarokcmie ,
Here's my modified version of the pbix file above, where you can create a column to calculate the duration of each row of data, and then go back and categorize it by SUBJECT to calculate the total duration of each SUBJECT.

Spend_hour = DATEDIFF('Table'[Received],'Table'[Sent],HOUR)
Spend by subject_hour = 
    CALCULATE(
        SUM('Table'[Spend_hour]),
        ALLEXCEPT(
            'Table',
            'Table'[Subject]
        )
    )
Spend_minute = DATEDIFF('Table'[Received],'Table'[Sent],MINUTE)
Spend by subject_minute = 
    CALCULATE(
        SUM('Table'[Spend_minute]),
        ALLEXCEPT(
            'Table',
            'Table'[Subject]
        )
    )

Final output

vheqmsft_0-1708914877388.png

If this is not the result you want, please clearly show the result you want in excel or screenshot form so we can help you faster

Best regards,

Albert He

 

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

 

Here is what I'm looking for :

 

Folder PathSubjectDateTimeSentDateTimeReceivedOutput
\Inbox\PA\PIProductionReview2/26/2024 8:002/26/2024 8:00Number of days/hours/minutes it took for this email with same subject line
\Inbox\PA\PIProductionReview2/23/2024 8:002/23/2024 8:00
\Inbox\PA\PIProductionReview2/22/2024 8:002/22/2024 8:00
\Inbox\PA\PIProductionReview2/21/2024 8:002/21/2024 8:00
\Inbox\PA\PIProductionReview2/20/2024 8:002/20/2024 8:00
\Inbox\PA\PIProductionReview2/16/2024 8:002/16/2024 8:00
\Inbox\PA\PIProductionReview2/15/2024 8:002/15/2024 8:00
\Inbox\PA\PIProductionReview2/14/2024 8:002/14/2024 8:00
\Inbox\PA\PIProductionReview2/13/2024 8:002/13/2024 8:00
\Inbox\PA\PIProductionReview2/12/2024 8:002/12/2024 8:00
\Inbox\PA\PIProductionReview2/9/2024 8:002/9/2024 8:00
\Inbox\PA\PIProductionReview2/8/2024 8:002/8/2024 8:00
\Inbox\PA\PIProductionReview2/7/2024 8:002/7/2024 8:00
\Inbox\PA\PIProductionReview2/6/2024 8:002/6/2024 8:00
\Inbox\PA\PIProductionReview2/5/2024 8:002/5/2024 8:00
\Inbox\PA\PIProductionReview2/2/2024 8:002/2/2024 8:00
\Inbox\PA\PIProductionReview2/1/2024 8:002/1/2024 8:00
\Inbox\PA\PIProductionReview1/31/2024 8:001/31/2024 8:00
\Inbox\PA\PIProductionReview1/30/2024 8:001/30/2024 8:00
\Inbox\PA\PIProductionReview1/29/2024 8:001/29/2024 8:00
\Inbox\PA\PIProductionReview1/26/2024 8:001/26/2024 8:00
\Inbox\PA\PIProductionReview1/25/2024 8:001/25/2024 8:00
\Inbox\PA\PIProductionReview1/24/2024 8:001/24/2024 8:00
\Inbox\PA\PIProductionReview1/23/2024 8:001/23/2024 8:00
\Inbox\PA\PIProductionReview1/22/2024 8:001/22/2024 8:00
\Inbox\PA\PIProductionReview1/19/2024 8:001/19/2024 8:00
\Inbox\PA\PIProductionReview1/18/2024 8:001/18/2024 8:00
\Inbox\PA\PIProductionReview1/17/2024 8:001/17/2024 8:00
\Inbox\PA\PIProductionReview1/16/2024 8:001/16/2024 8:00
\Inbox\PA\PIProductionReview1/12/2024 8:001/12/2024 8:00
\Inbox\PA\PIProductionReview1/11/2024 8:001/11/2024 8:00
\Inbox\PA\PIProductionReview1/10/2024 8:001/10/2024 8:00
\Inbox\PA\PIProductionReview1/9/2024 8:001/9/2024 8:00
\Inbox\PA\PIProductionReview1/8/2024 8:001/8/2024 8:00
\Inbox\PA\PIProductionReview1/5/2024 8:001/5/2024 8:00
\Inbox\PA\PIProductionReview1/4/2024 8:001/4/2024 8:00
\Inbox\PA\PIProductionReview1/3/2024 8:001/3/2024 8:00
\Inbox\PA\PIProductionReview1/2/2024 8:001/2/2024 8:00
\Inbox\AC\RE: Setup Request 1/24/2024 10:161/24/2024 10:16Number of days/hours/minutes it took for this email
\Inbox\AC\RE: Setup Request 1/23/2024 11:451/23/2024 11:45
\Inbox\AC\Setup Request 1/22/2024 12:061/22/2024 12:06
\Sent Items\Setup Request 1/25/2024 14:081/25/2024 14:08
\Sent Items\FW: Setup Request 1/23/2024 15:391/23/2024 15:39
\Sent Items\FW: Setup Request 1/23/2024 10:131/23/2024 10:12
\Sent Items\RE:  Setup Request 1/22/2024 16:451/22/2024 16:45
v-heq-msft
Community Support
Community Support

Hi @geeklarokcmie ,
Thanks to @wini_R  for troubleshooting the first problem. 
Here's how I troubleshoot the second problem
1. Create a measure

 

Measure = IF(SELECTEDVALUE(Query1[Sent].[MonthNo])= 1 ,"Janurary","")

 

2.Create a slicer

vheqmsft_0-1708593350267.png

3.The first method is to use subheadings for comments

vheqmsft_2-1708593689600.png

4.The second way is to use the detailed data lable for annotations

vheqmsft_3-1708593863051.png

Final output

vheqmsft_4-1708593900870.pngvheqmsft_5-1708593916295.png

 

Best regards,

Albert He

 

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

wini_R
Resolver II
Resolver II

Hi @geeklarokcmie,

 

As of your first question - you can calcuate this in power query using the following steps (just copy & paste it into advanced editor):

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLDoMgGAavQlw38QdRU3Y9gEn36gIpbUwsJIJJvX2hPutjSSbfMJDngZXGoob3sjXBJahVpT9F0fTCHQCHhIYECHUHDAxjFifolp2g2KPyslfqyimNVPZgCCkDOHZ6FJ05f5lC83GXzDsXEjFMF+UWpZPS6K4VEgn9kDslCfF13pFRec8GRGCDUo+8khtTv9TbPRU9OyVsrRVvFvkw91HDnG7Ey53033uYOv3oOug8dV1afgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t, FolderPath = _t, #"Date Received" = _t, #" Time Received" = _t, #"Date Sent" = _t, #"Time Sent" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Received", type date}, {"Date Sent", type date}, {" Time Received", type time}, {"Time Sent", type time}}),
    #"Merged Date and Time" = Table.CombineColumns(#"Changed Type", {"Date Received", " Time Received"}, (columns) => List.First(columns) & List.Last(columns), "Received"),
    #"Merged Date and Time1" = Table.CombineColumns(#"Merged Date and Time", {"Date Sent", "Time Sent"}, (columns) => List.First(columns) & List.Last(columns), "Sent"),
    #"Grouped Rows" = Table.Group(#"Merged Date and Time1", {"Subject"}, {{"aa", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "duration", each List.Max([aa][Sent]) - List.Min([aa][Received])),
    #"Expanded aa" = Table.ExpandTableColumn(#"Added Custom", "aa", {"FolderPath", "Received", "Sent"}, {"FolderPath", "Received", "Sent"}),
    #"Calculated Total Hours" = Table.TransformColumns(#"Expanded aa",{{"duration", Duration.TotalHours, type number}})
in
    #"Calculated Total Hours"

 

 

Outcome:

wini_R_0-1708555644392.png

 

 

Hello @wini_R - Thank you for providing the calculation.

 

However, my data actually looks like below where date and time are in same column and their data type is date/time. How can I tweak your calculation to get the desired results?

SubjectFolderPathDateTime ReceivedDate Time Sent
test layersinbox\lyc1/24/2024 10:11:56 AM1/24/2024 10:11:55 AM
test layersinobx\sent1/24/2024 10:07:00 AM1/24/2024 10:07:03 AM
test layersinbox\coa1/26/2024 11:53:14 AM1/26/2024 11:53:17 AM
source codeinbox\coa2/19/2024 12:53:14 PM2/20/2024 12:53:17 PM
assignment functionalinbox\2/1/2024 4:53:14 PM2/19/2024 4:53:17 PM
source codeinbox\sent2/20/2024 2:53:14 PM2/20/2024 2:53:17 PM

 

Hi @geeklarokcmie,

 

Please see if the following query helps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDLDsIgEEV/hbA2KSDa2J2JcWdi1F11QemomApJoUb/XmjxUVOXc8I9c4c8x9umuIB0eISXpiqhXgt39sNCONipK6ANSFA3KCNDLdyCdvgwyrED61AlHlBb/0Dpwtz3++oh/UATxhNGGEeUZJRmkymar4b4JPAhmSm8zIZVPymSZoQM2AIf/7O11aQRXWgaQ37/OKP8Levz9CWzpqklIGlK+JWxhM5iiEXZetVyRno8DTzIhLXqpK/+MHRstHTKaFF9tJ2zi/K+8bWJ94WD7eLHfWr8afdd7vAE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"DateTime Received", type datetime}, {"Date Time Sent", type datetime}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Subject"}, {{"tab2", each _, type table [Subject=nullable text, FolderPath=nullable text, DateTime Received=nullable datetime, Date Time Sent=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "duration", each List.Max([tab2][Date Time Sent]) - List.Min([tab2][DateTime Received])),
    #"Expanded aa" = Table.ExpandTableColumn(#"Added Custom", "tab2", {"FolderPath", "DateTime Received", "Date Time Sent"}, {"FolderPath", "Received", "Sent"}),
    #"Calculated Total Hours" = Table.TransformColumns(#"Expanded aa",{{"duration", Duration.TotalHours, type number}})
in
    #"Calculated Total Hours"

 

This doesn't seem to work for me at all. I run into syntax errors.
Can you tell me all the applied steps so I can create that in PowerQuery.

 

geeklarokcmie_0-1708629186787.png

 

It seems you just added one query into the other and ended up with 2 separate ones which cannot work. If you want to combine them, try to add the query above starting from 'Grouped rows' step and make sure it refers to the previous step in your current query.

Thank you for some reason. It still doesn't work for me. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.