Calculate total elapsed minutes

[Rigoleto]

I need help, I am really new in the Power BI, this is my question:

I need to do this scenario:

1. Set up my current datetime  to 7:00 am, for instance 20180614 7:00 am(Current time is 15:16)

2. Use the current date time 20180614 15:16 with the previous item to get the total minutes lapsed from 7:00 am to 15:16 pm

I really apreciate your help with this issue

🙂

[ChrisMendoza · Accepted solution]

Hello @Rigoleto,

At first glance I thought this was a simple problem however the issue for me was your datetime format beginning with '20180614 '.

The Query Editor should help you solve this:

let
    Source = /* I used an Excel file with a table named 'TableName' */
    TableName_Table = Source{[Item="TableName",Kind="Table"]}[Data],
    /* Step 1 Replace the " " with 'T' to fulfill YYYYMMDDThh:mm:ss.nnnnnnn format requirement for DateTime.FromText ( ) */
        #"Replaced Value" = Table.ReplaceValue(TableName_Table," ","T",Replacer.ReplaceText,{"StartDatetime"}),
    /* Step 2 use DateTime.FromText ( ) */
	#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each DateTime.FromText([StartDatetime])),
    /* Step 3 get the current time */
	#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.LocalNow()),
    /* Step 4 if it didn't recognize the Type, change the type to datetime */
	#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}, {"Custom", type datetime}}),
    /* Step 5 get the duration in minutes */
	#"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.2", each Duration.TotalMinutes([Custom.1]-[Custom])),
    /* Step 6 change the type */
	#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.2", type number}})
in
    #"Changed Type1"

[Rigoleto]

Thanks!!!!!

I have used the logic and it is running as my expectation