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.
Hi guys, I really need some help.
I need to calculate the time difference between the dates, but it doesn't work when the time is longer than one day.
Example :
As you can see on the first line, the Store was inactive on the date of 09 08/22 and was active on the date of 11 08/2022, it also has its opening hours, where I only calculate the time that is within this time .
In this example it was supposed to have a time of 19:40h but it calculates 39:40.
I really appreciate if anyone can help me.
Solved! Go to Solution.
Hi @fabiofurlanbr
Here is a sample with the solution https://www.dropbox.com/t/TxzEpjuToQycb9e4
Inactive Hour =
VAR StartDate = DATEVALUE ( Data[Date_Time_Clock_Inactive] )
VAR StartTime = TIMEVALUE ( Data[Date_Time_Clock_Inactive] )
VAR EndDate = DATEVALUE ( Data[Date_Time_Clock_Active] )
VAR EndTime = TIMEVALUE ( Data[Date_Time_Clock_Active] )
VAR StoreStartTime = TIMEVALUE ( Data[Start_Date_Store] )
VAR StoreEndTime = TIMEVALUE ( Data[Closed_Date_Store] )
VAR NumberOfDays = DATEDIFF ( StartDate, EndDate, DAY )
VAR NunOfDaysToUse = IF ( NumberOfDays = 0, 1, NumberOfDays ) - 1
VAR FirstDaySeconds = DATEDIFF ( StartTime, StoreEndTime, SECOND )
VAR LastDaySeconds = IF ( NumberOfDays > 0, DATEDIFF ( StoreStartTime, EndTime, SECOND ) )
VAR NormalDaySeconds = DATEDIFF ( StoreStartTime, StoreEndTime, SECOND )
VAR TotalSeconds =
FirstDaySeconds + LastDaySeconds + NormalDaySeconds * NunOfDaysToUse
VAR TotalMinutes = TotalSeconds/60
VAR Seconds = MOD ( TotalSeconds, 60 )
VAR Minutes = MOD ( TotalMinutes, 60 )
VAR Hours = QUOTIENT ( TotalMinutes, 60 )
RETURN
Hours & ":" & FORMAT ( Minutes, "00" ) & ":" & FORMAT ( Seconds, "00" )
Hi Tamerj
if you see the above column there repeating text values and i want to group them with a word like
if values bing / organic i want to group under Organic
if values accessories.mercedes-benz-mena.com / referral i want to group under referral
using if condition or swith condion
how can i do it
hi i have below colum
i want group the text values dynamically like
for referral = Referral
paid = Paid
social media = Social media
in paid i have cpc cpm ctr like that
how can i do the grouping
ga:sourceMedium
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
(direct) / (none) |
24sevenjobtalk.com / referral |
95.216.11.163 / referral |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
GMB / organic |
abo.mercedes-benz.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
accessories.mercedes-benz-mena.com / referral |
accessories.mercedes-benz-mena.com / referral |
accessories.mercedes-benz-mena.com / referral |
accessories.mercedes-benz-mena.com / referral |
aindubai.com / referral |
alfahim.com / referral |
alfahim.com / referral |
alfahim.com / referral |
alfahim.com / referral |
almullagroup.com / referral |
api.taboola.com / referral |
axonius.lightning.force.com / referral |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
bing / organic |
Hi @vijaykumargade
What do you exactly mean by "Group Dynamically"? Please clarify by providing examples of the expected result. Thank you
Hi Tamerj
below is the column and respective values
(direct) / (none) |
10.7.131.30:15871 / referral |
114.114.114.114:9421 / referral |
2338d3981f5a022ab2cbdf8af2067799.safeframe.googlesyndication.com / referral |
2e745ed74c8263080a32e0c24cc1ec5d.safeframe.googlesyndication.com / referral |
30c73e63ce2579dca7f25bbc9bb39d9a.safeframe.googlesyndication.com / referral |
53.103.54.13 / referral |
5d8b7c7f9d7a026561c5fdf2648c0c6e.safeframe.googlesyndication.com / referral |
5e4afd315ba3228cc0e6f91c60aed256.safeframe.googlesyndication.com / referral |
6920e283ba54cd9dfb3a27d4c85bcc36.safeframe.googlesyndication.com / referral |
6a9ed14b61e67e8d3b674f2a64825f91.safeframe.googlesyndication.com / referral |
72bb0856134c20c1fc1b486a2c4a76a9.safeframe.googlesyndication.com / referral |
79e5447a12a0daf20bf9e62d80a6dc36.safeframe.googlesyndication.com / referral |
97ee5ee1ae1979fc5b44e95d4c3b2572.safeframe.googlesyndication.com / referral |
9b5b40726c4092f3ebc15afc9932fa87.safeframe.googlesyndication.com / referral |
aax.amazon-adsystem.com / referral |
aax-fe-sin.amazon-adsystem.com / referral |
abu-dhabi.mercedes-benz-mena.com / referral |
abu-dhabi.pe-mb.com / referral |
adform.com / referral |
ads.google.com / referral |
Adscholars / Display |
adscholars.trackier.com / referral |
adsintegrity.net / referral |
ae.kompass.com / referral |
aec-excel.officeapps.live.com / referral |
alfaromeo-dubai.com / referral |
alfaromeo-uae.com / referral |
alhaddadmotor.com / referral |
aljazeera.com / referral |
alohafind.com / referral |
analytics.google.com / referral |
api.whatsapp.com / referral |
apn-portal.my.salesforce.com / referral |
app.ahrefs.com / referral |
app.asana.com / referral |
app.leadsgorilla.net / referral |
app.meishi.me / referral |
app.seez.co / referral |
app.zoominfo.com / referral |
app-eu.sparkcentral.com / referral |
arabveturk.com / referral |
assets.oneweb.mercedes-benz.com / referral |
author.myconnect.ae / referral |
autodrift.ae / referral |
away.vk.com / referral |
baidu.com / referral |
befdubybef / evfcybl |
bing / organic |
c41f84f5987ea6e7668cca854ff7ce3c.safeframe.googlesyndication.com / referral |
c93dd53d764c4219ac499963103c44f9.safeframe.googlesyndication.com / referral |
ca.search.yahoo.com / referral |
ca44761381d60824e0e1704329c3c9e3.safeframe.googlesyndication.com / referral |
callgear.amocrm.ru / referral |
captcha.gecirtnotification.com / referral |
carguide.ae / referral |
cbsearch.site / referral |
ccavenue.ae / referral |
checkout.lightning.force.com / referral |
clickserve.dartsearch.net / referral |
cloudera.lightning.force.com / referral |
cn.bing.com / referral |
coccoc.com / referral |
com.google.android.gm / referral |
com.google.android.googlequicksearchbox / referral |
com.linkedin.android / referral |
com.slack / referral |
confluence.mercedes-benz.io / referral |
connect.themediatrust.com / referral |
copy_link / android |
creative-preview-an.com / referral |
crm.preqin.com / referral |
cse.google.com / referral |
cstool.www.linkedin.com / referral |
daily-stuff.com / referral |
daimlerasia.force.com / referral |
ddei3-0-ctp.trendmicro.com / referral |
devgargash.wpengine.com / referral |
dfa / cpm |
dfa / social_paid |
dnb.lightning.force.com / referral |
docs.qq.com / referral |
dotwnews.com / referral |
dubai.mercedes-benz-mena.com / referral |
dubizzle.lightning.force.com / referral |
duckduckgo.com / referral |
e.issuu.com / referral |
e000194.reu1.blindsidenetworks.com / referral |
e1ed1ddf24e029c68eb7df0988bb829d.safeframe.googlesyndication.com / referral |
ea6c3d273206d477529e38faf7ce95d3.safeframe.googlesyndication.com / referral |
eb9b74a41e4d769a435338c3db1c0789.safeframe.googlesyndication.com / referral |
ecosia.org / referral |
edd6e43693a4a0465b2bca7c2ef00e49.safeframe.googlesyndication.com / referral |
enquiry-manager-2.netdirector.co.uk / referral |
equinix.lightning.force.com / referral |
eu-west-1a.online.tableau.com / referral |
explore.reference.com / referral |
f931bb1bd8264b9cd87391e87bee6edd.safeframe.googlesyndication.com / referral |
facebook / social |
facebook.com / referral |
Facebook-Instagram / social_paid |
falconsocial.lightning.force.com / referral |
filter.nov.com / referral |
gac-motor.com / referral |
gargash.ae / referral |
gargash-ar-motors.auto.gforcestestlink.com / referral |
gargashgacmotor.com / referral |
gargashold-motors.auto.gforcestestlink.com / referral |
gateway.zscloud.net / referral |
gforces1.zendesk.com / referral |
globalboarding.g2netview.com / referral |
globaltrendmonitor.com / referral |
google / (not set) |
google / cjac |
google / cpc |
google / discovery |
google / gdn |
google / organic |
google / performax |
google / pfmax |
google / pmax |
google / ppc |
google / search |
google / sem |
google / YouTube_Bumper |
google / YouTube_Trueview |
googleads.g.doubleclick.net / referral |
googleadservices.com / referral |
googlechrome.github.io / referral |
googleusercontent.com / referral |
greytip.membrain.com / referral |
gumtree.com.au / referral |
https://jobsarchives.com/ / (not set) |
ig / social |
IG ShopNow Button / IG Social |
in.search.yahoo.com / referral |
insights.hotjar.com / referral |
Instagram / IG Social |
Instagram / INS_feed |
instagram / social |
instagram.com / referral |
int.search.tb.ask.com / referral |
internalfb.com / referral |
issuu.com / referral |
it.search.yahoo.com / referral |
jcrm.trafficterminal.com / referral |
jira.mercedes-benz.io / referral |
jira.netdirector.co.uk / referral |
jobscareers4u.com / referral |
kijiji.ca / referral |
kimblog.top / referral |
kwork.ru / referral |
l.facebook.com / referral |
l.instagram.com / referral |
l.wl.co / referral |
lavasoft.gosearchresults.com / referral |
lens.google.com / referral |
link.2gis.ru / referral |
link.zhihu.com / referral |
linkedin / social |
LinkedIn / social_paid |
linkedin.com / referral |
linktr.ee / referral |
lm.facebook.com / referral |
lnkd.in / referral |
locken7w.beget.tech / referral |
login.microsoftonline.com / referral |
loky.ch / referral |
m.facebook.com / referral |
m.gsearch.co / referral |
m.nearbyme.io / referral |
m.youtube.com / referral |
mail.gargour.com / referral |
mail.gargour.com.jo / referral |
mail.google.com / referral |
mail2world.com / referral |
malaysia.search.yahoo.com / referral |
marketmefamous.com / referral |
mbworld.org / referral |
mercedes-benz-ar-gargash.auto.gforcestestlink.com / referral |
mercedes-benz-mena.com / referral |
mercedes-benz-trucks.com / referral |
mercedetrucksmena-dev.azurewebsites.net / referral |
meta / reach |
meta / social |
mrs.arachnys.com / referral |
msn.com / referral |
mybib.com / referral |
mycima.ac / referral |
myhealthgazette.com / referral |
navbharattimes.indiatimes.com / referral |
netdirector.co.uk / referral |
nortonsafe.search.ask.com / referral |
notify.bluecoat.com / referral |
nutanix.lightning.force.com / referral |
nutanix.my.salesforce.com / referral |
OMGProgrammatic / display |
OnlineVideo / YouTube |
org.telegram.messenger / referral |
outlook.live.com / referral |
partnerdash.google.com / referral |
petalsearch.com / referral |
pjp-site.ukit.me / referral |
poshukach.com / referral |
preowned.gargash.ae / referral |
presearch.com / referral |
prod-author.mbmxp.aem.oneweb.mercedes-benz.com / referral |
pubads.g.doubleclick.net / referral |
purplepreowned.ae / referral |
qwant.com / referral |
reader.magzter.com / referral |
rediffmail.com / referral |
s.newsearchers.com / referral |
s0.2mdn.net / referral |
s3arch.page / referral |
se.search.yahoo.com / referral |
Search / Google |
search.aol.co.uk / referral |
search1.me / referral |
search-7.com / referral |
searchbip.com / referral |
search-dra.dt.dbankcloud.com / referral |
searchguide.level3.com / referral |
secure.ccavenue.ae / referral |
secureacceptance.cybersource.com / referral |
semrush.com / referral |
SFMC / Email |
SFMC / SMS |
sg.search.yahoo.com / referral |
sharjahart.org / referral |
shorteh.com / referral |
sitelike.org / referral |
snapchat.com / referral |
Social / MixedPlacement |
sourcescrub.com / referral |
stagegargash.wpengine.com / referral |
startpage.com / referral |
statics.teams.cdn.office.net / referral |
storage.googleapis.com / referral |
streamtape.net / referral |
surf.bluetouch.hk / referral |
symphonysummit.lightning.force.com / referral |
t.co / referral |
tagassistant.google.com / referral |
talonone.lightning.force.com / referral |
tbbtyf / cpc |
tbbtyf / ffbedu |
tbbtyf / pmax |
teams.microsoft.com / referral |
teoma.co.uk / referral |
textunited.com / referral |
thesun.co.uk / referral |
tiktok.com / referral |
TMV / display |
tools.publicis.sapient.com / referral |
top.allresultsweb.com / referral |
toto.henkelgroup.net / referral |
touch.facebook.com / referral |
tr.search.yahoo.com / referral |
translate.googleusercontent.com / referral |
trello.com / referral |
trucks.mercedesbenzmena.com / referral |
TW / TW_image |
TW / TW_video |
twitter / cpm |
twitter / cpv |
ugroocuw.net / referral |
uk.search.yahoo.com / referral |
ukc-excel.officeapps.live.com / referral |
url-opener.com / referral |
us.search.yahoo.com / referral |
usc-excel.officeapps.live.com / referral |
user-content.s7.sfmc-content.com / referral |
vidspeeds.com:2053 / referral |
view.mena.mercedes-benz-mena.com / referral |
web.skype.com / referral |
webcache.googleusercontent.com / referral |
websitedownloader.io / referral |
wholepagesbs.blob.core.windows.net / referral |
work.molardata.com / referral |
ww0.0gomovies.so / referral |
www-gac--motor-com.translate.goog / referral |
yahoo / organic |
yammer.com / referral |
yandex / organic |
yandex.ru / referral |
yasmarinacircuit.com / referral |
you.com / referral |
youtube / video |
youtube.com / referral |
zarebin.ir / referral |
zoominfo.com / referral |
i am using swith formula to group the tex values shown in above column but i am getting result as Others in the newly created coulmn
Hi Tamerj
if you see the above column there repeating text values and i want to group them with a word like
if values bing / organic i want to group under Organic
if values accessories.mercedes-benz-mena.com / referral i want to group under referral
using if condition or swith condion
how can i do it
Not sure ifI fully understand. Perhaps a calculated column like this?
GroupCoulmn =
VAR String = 'Table'[Columm]
VAR Items =
SUBSTITUTE ( String, " / ", "|" )
RETURN
PATHITEM ( Items, 2 )
Hi
i have coulnm name yearMonth in below formate
202210
how can i convert it into mmm-yy in power query
you can try
FORMAT ( DATE ( LEFT ( 'Table'[Column], 4 ), RIGHT ( 'Table'[Column], 2 ), 1 ), "MMMM-YY" )
Hi @fabiofurlanbr
Here is a sample with the solution https://www.dropbox.com/t/TxzEpjuToQycb9e4
Inactive Hour =
VAR StartDate = DATEVALUE ( Data[Date_Time_Clock_Inactive] )
VAR StartTime = TIMEVALUE ( Data[Date_Time_Clock_Inactive] )
VAR EndDate = DATEVALUE ( Data[Date_Time_Clock_Active] )
VAR EndTime = TIMEVALUE ( Data[Date_Time_Clock_Active] )
VAR StoreStartTime = TIMEVALUE ( Data[Start_Date_Store] )
VAR StoreEndTime = TIMEVALUE ( Data[Closed_Date_Store] )
VAR NumberOfDays = DATEDIFF ( StartDate, EndDate, DAY )
VAR NunOfDaysToUse = IF ( NumberOfDays = 0, 1, NumberOfDays ) - 1
VAR FirstDaySeconds = DATEDIFF ( StartTime, StoreEndTime, SECOND )
VAR LastDaySeconds = IF ( NumberOfDays > 0, DATEDIFF ( StoreStartTime, EndTime, SECOND ) )
VAR NormalDaySeconds = DATEDIFF ( StoreStartTime, StoreEndTime, SECOND )
VAR TotalSeconds =
FirstDaySeconds + LastDaySeconds + NormalDaySeconds * NunOfDaysToUse
VAR TotalMinutes = TotalSeconds/60
VAR Seconds = MOD ( TotalSeconds, 60 )
VAR Minutes = MOD ( TotalMinutes, 60 )
VAR Hours = QUOTIENT ( TotalMinutes, 60 )
RETURN
Hours & ":" & FORMAT ( Minutes, "00" ) & ":" & FORMAT ( Seconds, "00" )
"it also has its opening hours, where I only calculate the time that is within this time" would you please elaborate on this. It is not so clear how the timw is supposed to be calculated. Also please share the same sample smdata in a copy/paste format in order to use it in a sample file. Thank you
Hi @tamerj1 ,
First thank you very much.
I need to calculate the Store's downtime, however, the store has an opening time and closing time. I need to calculate the downtime when the event occurred within the Store's opening hours.
Example:
The store opens at 7 and closes at 21.
The Event started at 7pm on the date of 09/08 and ended at 11:16h on the date of 11/08.
I need to calculate the time that was inactive within the opening and closing hours of the store.
The Event that started on 09/08 at 19:36hs and ended on 11/08 at 11:16h .
It should have an unavailability of 19:40Hs , but it shows 39:40.
I can't calculate for events longer than one day in duration.
Store | Date_Time_Clock_Inactive | Date_Time_Clock_Active | Start_Date_Store | Closed_Date_Store | Inactive_Time.Sec. | Inactive Hour |
Brascan Open Mall | 09/08/2022 19:36 | 11/08/2022 11:16 | 09/08/2022 07:00 | 11/08/2022 21:00 | 142801 | 39:40:01 |
Brascan Open Mall | 09/08/2022 17:13 | 09/08/2022 18:13 | 09/08/2022 07:00 | 09/08/2022 21:00 | 3602 | 01:00:02 |
Brascan Open Mall | 09/08/2022 18:47 | 09/08/2022 18:51 | 09/08/2022 07:00 | 09/08/2022 21:00 | 240 | 00:04:00 |
Brascan Open Mall | 09/08/2022 19:01 | 09/08/2022 19:04 | 09/08/2022 07:00 | 09/08/2022 21:00 | 181 | 00:03:01 |
Brascan Open Mall | 09/08/2022 18:18 | 09/08/2022 18:19 | 09/08/2022 07:00 | 09/08/2022 21:00 | 61 | 00:01:01 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |