Solved: Re: Calculate date and time difference greater tha...

fabiofurlanbr · ‎08-13-2022

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.

tamerj1 · ‎08-14-2022

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" )

View solution in original post

vijaykumargade · ‎02-04-2023

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

View solution in original post

vijaykumargade · ‎02-03-2023

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)

24sevenjobtalk.com / referral

95.216.11.163 / referral

GMB / organic

abo.mercedes-benz.com / referral

abu-dhabi.mercedes-benz-mena.com / referral

accessories.mercedes-benz-mena.com / referral

aindubai.com / referral

alfahim.com / referral

almullagroup.com / referral

api.taboola.com / referral

axonius.lightning.force.com / referral

bing / organic

tamerj1 · ‎02-03-2023

Hi @vijaykumargade
What do you exactly mean by "Group Dynamically"? Please clarify by providing examples of the expected result. Thank you

vijaykumargade · ‎02-04-2023

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

Channels = SWITCH(

TRUE(),

reportoutput[sourceMedium] = "referral", "Referral",

reportoutput[sourceMedium] = "paid", "Paid",

reportoutput[sourceMedium] = "cpc", "Paid",

reportoutput[sourceMedium] = "searach", "Paid",

reportoutput[sourceMedium] = "cpm", "Paid",

reportoutput[sourceMedium] = "pfmax", "Paid",

reportoutput[sourceMedium] = "cpv", "Paid",

reportoutput[sourceMedium] = "pmax", "Paid",

reportoutput[sourceMedium] = "social_paid", "Paid",

reportoutput[sourceMedium] = "social", "Social",

reportoutput[sourceMedium] = "IG social", "Social",

reportoutput[sourceMedium] = "YouTube_Bumper", "Social",

reportoutput[sourceMedium] = "YouTube_Trueview", "Social",

reportoutput[sourceMedium] = "INS_feed", "Social",

reportoutput[sourceMedium] = "YouTube", "Social",

reportoutput[sourceMedium] = "TW_image", "Social",

reportoutput[sourceMedium] = "TW_video", "Social",

reportoutput[sourceMedium] = "video", "Social",

reportoutput[sourceMedium] = "organic", "Organic",

reportoutput[sourceMedium] = "Display", "Display",

"Others"

)

could you please help me

vijaykumargade · ‎02-04-2023

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

tamerj1 · ‎02-04-2023

@vijaykumargade

Not sure ifI fully understand. Perhaps a calculated column like this?

GroupCoulmn =
VAR String = 'Table'[Columm]
VAR Items =
SUBSTITUTE ( String, " / ", "|" )
RETURN
PATHITEM ( Items, 2 )

vijaykumargade · ‎01-27-2023

Hi

i have coulnm name yearMonth in below formate

202210

how can i convert it into mmm-yy in power query

tamerj1 · ‎01-27-2023

Hi @vijaykumargade

you can try

FORMAT ( DATE ( LEFT ( 'Table'[Column], 4 ), RIGHT ( 'Table'[Column], 2 ), 1 ), "MMMM-YY" )

tamerj1 · ‎08-14-2022

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" )

fabiofurlanbr · ‎08-16-2022

Hi @tamerj1

Thank you so much, you saved my job.

It's perfect

tamerj1 · ‎08-13-2022

Hi @fabiofurlanbr

"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

fabiofurlanbr · ‎08-13-2022

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

Calculate date and time difference greater than one day - Help

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024