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
Anonymous
Not applicable

Calculate seconds based on 2 dates

Looking for a DAX expression to calculate StartSeconds and EndSeconds based on 2 dates. This is currently being done in Excel, the user updates the dates and it calculates the seconds.  I would like to dynamically do this based on Start Date is Today() - 7 and the EndDate is Today().  I need to pass these values in Seconds to an API web call.

 

Capture.PNG

tempsnip.png

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@Anonymous ,

Let's see if this can help you out. I attached the pbix file below. But here's a few bullet points of what I did:

  • Created two parameters. One for Date0 and one for NumOfDays
  • Created a table that will have the Date and Time now
  • Added two custom columns that will take the total seconds from Date0 and the date today and then one that will use the NumOfDays parameter to offset the Date today and then givet those total seconds.

 

Here's the M code:

let
    Query2 = #table(
    type table
    [
        #"Now" = date
        
    ],
{
    {DateTime.LocalNow()}
    
}

),
    #"Added Custom" = Table.AddColumn(Query2, "StartSeconds", each Duration.Days (  Date.From( [Now] ) - Date0) *24 *60*60, Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "EndSeconds", each Duration.Days (Date.From ( [Now] ) + #duration( NumOfDays,0,0,0) - Date0)*24*60*60, Int64.Type ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"StartSeconds", "EndSeconds"})
in
    #"Removed Other Columns"

and then the final table

Final Table.png

 

Here's the pbix file:

https://1drv.ms/u/s!Amqd8ArUSwDS00jGX8xV_zSn2zEX

 

Hope it helps

-Nick

View solution in original post

Anonymous
Not applicable

I was able to accomplish what I needed y just calculating the values like below.


BegSecValue = (Duration.Days(Date.From(DateTime.Date(DateTime.LocalNow())) - Date.From("1/1/1970")) -1) *24 *60*60,
EndSecValue = (Duration.Days(Date.From(DateTime.Date(DateTime.LocalNow())) - Date.From("1/1/1970")) +1) *24 *60*60,

 

Another issue - I am unable to figure out how to sort this matrix based on the date ranges below.  I have tried a custom sort buat that did not seem to work. Any ideas?

Capture.PNG

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

@Anonymous ,

Let's see if this can help you out. I attached the pbix file below. But here's a few bullet points of what I did:

  • Created two parameters. One for Date0 and one for NumOfDays
  • Created a table that will have the Date and Time now
  • Added two custom columns that will take the total seconds from Date0 and the date today and then one that will use the NumOfDays parameter to offset the Date today and then givet those total seconds.

 

Here's the M code:

let
    Query2 = #table(
    type table
    [
        #"Now" = date
        
    ],
{
    {DateTime.LocalNow()}
    
}

),
    #"Added Custom" = Table.AddColumn(Query2, "StartSeconds", each Duration.Days (  Date.From( [Now] ) - Date0) *24 *60*60, Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "EndSeconds", each Duration.Days (Date.From ( [Now] ) + #duration( NumOfDays,0,0,0) - Date0)*24*60*60, Int64.Type ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"StartSeconds", "EndSeconds"})
in
    #"Removed Other Columns"

and then the final table

Final Table.png

 

Here's the pbix file:

https://1drv.ms/u/s!Amqd8ArUSwDS00jGX8xV_zSn2zEX

 

Hope it helps

-Nick

Anonymous
Not applicable

Interesting but I was hoping to automate the process to be able to pass the values to an API call.  I may be able to leverage what you did and just hard code some values in.  The process will always look back at the last 7 days to current date.

 

let
BegSecValue = 1558224000,

EndSecValue = 1558396799,

Source = Csv.Document(Web.Contents("https://csv.xml?username=xxxx&password=xxxx&form_id=1233&begin_second=" & ""&Number.ToText(BegSecValue)&"" & "&end_second=" & ""&Number.ToText(EndSecValue)&""),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]),

#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Start Date] <> "Start Date") and ([Action] = "DELETE" or [Action] = "REPLACEMENT"))
in
#"Filtered Rows"

Anonymous
Not applicable

What if you drill down to get a separate query for StartSeconds and End Seconds and then pass those in the API:

Drill Down.png

 

 

Anonymous
Not applicable

Let me try that, if the StartSeconds and EndSeconds will allway be upated first then I may be able to pull the values from that table.

 

Thanks for all your help!!

Anonymous
Not applicable

They are. Everything starts with the DateTime.Local() function which gets updated on refresh

Anonymous
Not applicable

How would I load those values into these variables?

 

let
BegSecValue = 1558224000,

EndSecValue = 1558396799,

Source = Csv.Document(Web.Contents("https://csv.xml?username=xxxx&password=xxxx&form_id=1233&begin_second=" & ""&Number.ToText(BegSecValue)&"" & "&end_second=" & ""&Number.ToText(EndSecValue)&""),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]),

#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Start Date] <> "Start Date") and ([Action] = "DELETE" or [Action] = "REPLACEMENT"))
in
#"Filtered Rows"

Anonymous
Not applicable

Try this code out. Though instead of drilling down as new, you want to drill down only. One for StartSeconds and One for EndSeconds. You can than pass these into your query

Pass Parameters.png

 

let

Source = 
Csv.Document(
    Web.Contents(
        "https://csv.xml?username=xxxx&password=xxxx&form_id=1233&begin_second=&" 
        &StartSeconds&
        "" & "&end_second=" & ""
        &EndSeconds&""),
        [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]
        ),

#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Start Date] <> "Start Date") and ([Action] = "DELETE" or [Action] = "REPLACEMENT"))
in
#"Filtered Rows"

Here's the file:

https://1drv.ms/u/s!Amqd8ArUSwDS00yGGrDBMprBxw7s

Anonymous
Not applicable

I was able to accomplish what I needed y just calculating the values like below.


BegSecValue = (Duration.Days(Date.From(DateTime.Date(DateTime.LocalNow())) - Date.From("1/1/1970")) -1) *24 *60*60,
EndSecValue = (Duration.Days(Date.From(DateTime.Date(DateTime.LocalNow())) - Date.From("1/1/1970")) +1) *24 *60*60,

 

Another issue - I am unable to figure out how to sort this matrix based on the date ranges below.  I have tried a custom sort buat that did not seem to work. Any ideas?

Capture.PNG

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.