Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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:
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
Here's the pbix file:
https://1drv.ms/u/s!Amqd8ArUSwDS00jGX8xV_zSn2zEX
Hope it helps
-Nick
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?
@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:
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
Here's the pbix file:
https://1drv.ms/u/s!Amqd8ArUSwDS00jGX8xV_zSn2zEX
Hope it helps
-Nick
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"
What if you drill down to get a separate query for StartSeconds and End Seconds and then pass those in the API:
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!!
They are. Everything starts with the DateTime.Local() function which gets updated on refresh
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"
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
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:
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |