DAX calculation for local time versus UTC

DAX is Microsoft’s powerful language behind Excel and Power BI.  Very few users of Excel know that you can utilize it, but it’s predominately available in one of the most underutilized features called PowerPivot.  Fortunately DAX is used also in Power BI and while creating a report for use by a customer I needed to translate log times from local time and compare it to UTC time.

Problem: Our logs store the date and time in the local Central Standard Time Zone (CST).  Everything works great locally, where I have my PC set to CST.  However when I publish my report to Power BI, they use UTC time, and for 6 hours today’s data becomes completely unavailable.

Reason: CST is Universal Time minus 6 hours (depending on daylight savings time).  So at 6pm CST today, it becomes 12am UTC tomorrow.

Solution: Here’s the formula I used

Today = IF(DATE(YEAR([CreatedTime]),MONTH([CreatedTime]),DAY([CreatedTime])) = NOW() – TIME(6,0,0), TRUE, FALSE)

We have to remove the time from my data ([CreatedTime]), then take the current Date and time, and subtract 6 hours.  If the Date matches, then we know it’s today.

read more

Spam domains

Spam domains

So recently I’ve had some issues with people calling and emailing trying to get my business for outsourcing.  Looks like my information was “scraped” from LinkedIn.  In an effort to help others, here is a list of domains that I’m blocking.  I’ll keep it updated as I block them from our email server.  I’ll put the newer domains at the top.

It’s important to note that a majority of these people are all ignoring Federal laws that require unsolicited emails to require a way to unsubscribe, they are blatantly sending out emails without any prior connection with our company, or ignoring unsubscribe requests.

Update 4/7/17 – I’m also now including domains that are being used from spammers, phishing attempts, domains hacking WordPress registration, and blatant marketing attempts.

Update 5/23/17 – You can be rest assured that anything from these domains can go straight to the delete box.  I’m also posted some Phishing scams and domains of the URL that they link to

Updated 5/23/17
shebakconf.ir
systemverification-apple.com
apple-useraccountvalidation.com
lvcoxmail.com
rene-schulte.info
mailinator.com
matrix.com.br
ocn.ne.jp
cms.k12.nc.us
prohumandesign.ru
pineva.top
121-leads.com
lendini.net
snd47.ch
onlinecompliancepanel.net
regulatorywebinar.com
helphearing.info
cd-tees.com
sparkhit.com
manzanillorealestate.com
morissrl.it
office365-email-viewer.com
deniskovacevic.ch
sfunds.net
lendini.net
netnut.io
divinetworks.com
jaean.stream
robertocervoni.it
prorata.com
salesloft.com
matinsa.cl
sailingmonea.com
mediom.com
topcrickettips.com
vinotrans.com
callgirlindelhineha.com
industrialization.go.ke
axisj.com
weinberg.fr
klkgraphics.com
link.net
hairlebration.com
vicimarketing.com
dawrtla2y.com
zknic.cn
tms320.cn
acquisition-intl.com
cloudinary.com
c2cemail.com
hacsrx.com
buckhanan.com
kingiv.training
advids.net
advids.style
gnetmail.co.za
rrevrss.us
022china.com
herbalpillseshop.ru
tw-24.pl
asiaoilgroup.com
thebanyantreecenter.com
ghostprint.bio
happynarae.co.kr
weboriginal.eu
najlepszabaza.pl
ssmfa.com
sud-urist.ru
partenaireaffaires.com
yazd.ac.ir
xn–9myy8htrgt8r.com
Fedex-international.com
037fx.org
textiledesigners.top
cytekboi.com
leosnijders.nl
sapperconsulting.us
emailcontent2.com
tegart.top
raddar.com.br
nptco.org
hkp.hu
gtoken05.mobi
netnut.io
divinetworks.com
merchantcapitalbroker.com
americanmerchantservices.net
moneyeveryoneneedsit.com
actices.com
scoldly.com
spamavert.com
bluetwopages.com
wanadoo.co.uk
sharepointreviews.com
000.com
nuzzel.com
sapperconsulting.us
bessttoffrr.us
amepe.net
cludcmputng.us
embaland.top
installaware.com
orange.net
dishsubmit.us
bluetwopages.biz
poplamp.com
eta.fr
themindo.com
netregsrvcs269.com
tjap.jus.br
greattbbrain.us
ajsph.org
painrlief.us
projectlambda.com
nincsmail.com
daxx-staffing.com
outboundpros.com
advidsagency.co
receiveipart.top
onlinecompliancepanel.net
netsrvcs519.com
iafpa.org
iqcs-sa.cn
twoomail.com
gol.com
sapperconsulting.us
tin.it
elfinanciero.com.mx
aitglobalinc.com
daxx-staffing.com
koali-leads.com
techzweb.com
hklaw.com
rankthetop.com
emailchoice.com
eicher.co.in
restachange.date
ultracons.com
hrwallingford.co.uk
zombiesbase.com
yandex.com
o2.pl
mailcatch.com
mail.ru
krabbed.com
i.krabbed.com
de.abrupter.com
bongobongo.nut.cc
163.com
resistingmoney.com
pairst.com
marrived.com
mantly.com
ls.lv
miomirex.ru
studio98mail.com
walla.co.il
chunkbait.com
meddi.bid
1105info.net
elatoinlighting.com
ringout-cs.com
4kadl.net
ufafz.info
gettharrd.us
london.hhuq.net
breastgrw.us
humenews.com
lalakskahj.us
quickfix-capital.com
kernicc.us
leads-ignite.com
jafp.xyz
webcompliancepanel.com
sb4sk.org
nea.gov.sg
gkclasses.com
southwestgreens.com
whitehorsevideo.com
shortcome.stream
activelink20.com
technologylearningreview.com
fundingcirclemail.com
hupertyou.stream
higher-standards.net
corp-vis.com
turninfluences.stream
quickfix-capital.com
ovillio.com
vincheckjon.pro
stateeworld.date
prorata.com
financiallyinformed.com
insourcepro.net
yandex.com
hp168.org
adelphia.com
loudouninsurancegroup.com
motli.org
88828.pub
quickfix-capital.com
stemy.org
scfwv.org
mailboy.org
hirearesourcepool.com
housat.com
mksnet.com.br
massiveb2b.com
cavecreek.net
softpower.com
prorata.com
infographic-world.com
healthcaremodelbook.com
manifrast.bid
elitejobalerts.com
themerchantlendr.com
adelphia.com
loudouninsurancegroup.com
motli.org
88828.pub
quickfix-capital.com
stemy.org
scfwv.org
mailboy.org
hirearesourcepool.com
housat.com
mksnet.com.br
massiveb2b.com
cavecreek.net
tourist.gnamaze.us
flightstmltr.us
restorhair.us
premiermerchantservicesgroup.com
braelyn-inc.com
ykkg.net
techzweb.com
ejalerts.com
sesetty.com
advids.io
guctech.com
praxisescrow.com
rimuhosting.com
surgesoftwaredev.com
softlayer.com
tmt-news.com

read more

Use VS Code to create PowerShell scripts to move files into folders by date

Recently while cleaning up a bunch of old hard drives and getting ready to sell / give them away, I found a ton of old photos.  Unfortunately I found that many of them had the same file names and weren’t sorted at all.  In fact, I had no way of filtering them in any way meaningful.  I resigned myself to manually create folders by day and throw them into the folders.  That lasted all of 5 minutes.

VS Code (Visual Studio Code) is a great little program that you can write software with.  It’s lightweight, has debugging, extensions, code complete / IntelliSense (when you type, it will help finish your word), and best of all it’s FREE!

Task: Create folders based off of the modified date of photos, then move the photo into the folder

Example:

image

As you can see from the screenshot above, we have 3 different dates.  I want this picture put into the folder named 2015-01-30 because that’s the best date that can be found according to when the picture was taken, not when it was moved or “created” because obviously those dates are wrong.

So let’s see how we can do this.

Installing VS Code

Navigate over to https://code.visualstudio.com and download the software.  You’ll want to do this on Windows, although VS Code will work on Mac, Linux, and Windows.

Once installed, then you want to run it and click the bottom left button and install the PowerShell extension.  Screenshot below.

image

  • Step 1: Click on the extensions icon
  • Step 2: Search for PowerShell
  • Step 3: Click on the extension (you can skip this)
  • Step 4: Click on Install
  • Step 5: Click on Reload
  • Step 6: Click on Reload Window

Coding

We want to create a new file and give it a name with the extension of ps1.  The extension makes sure Windows knows it’s a PowerShell script.

If we write the code and save it:

Write-Host “Hello World”

VS Code prompts us to create a launch.json file.  This is just to help VS Code run the script and enable debugging so we can step through the code if we need to.  The only thing that the configuration needs is the ps1 filename.  We replace the word Script twice with the filename that we’re running.

After that we can run and debug to our heart’s content.  Here’s the following code that I wrote to accomplish the task:

$SourceDir = “C:\Users\RandyWalker\Desktop\Pictures\”
$DestinationDir = “C:\Users\RandyWalker\Desktop\FolderPictures\”

foreach ($file in get-childitem $SourceDir .)
{
    $Directory = $DestinationDir + $file.LastWriteTime.ToString(“yyyy-MM-dd”)
    $FullDestinationDir

    if (!(Test-Path $Directory))
    {
        Write-Host “Created Directory : ” $DestinationDir $directory
        New-Item $directory -type directory
    }

    Write-Host “Moving file : ” $file.fullname ” to ” $directory
    Move-Item $file.fullname $directory
}

Naturally you’ll want to change the $SourceDir and $DestinationDir to the folders you want.  That’s pretty much all you’ll need to do.  I manually placed the folders created into buckets of year, but I could have just as easily modified the code to also create a folder by year.  Enjoy!

read more

Continuous Integration with VSTS – Tips #1

Now that the blog is officially announced and released, I thought I’d show you a quick tip in how we were able to update our main website through continuous integration and continuous deployment.  Since this particular website is used just for our marketing purposes, we currently aren’t using deployment slots, so this helps us out a great deal.

All of our code is in GitHub, which I’ll blog about later.  The real issue in getting our Web App deployed took us several weeks with a ticket waiting with Microsoft support.  In the end it was worth the wait, and as you can see, with this posting it’s now live.

We’ve purposefully put all of our Builds under one Repo in VSTS (Visual Studio Team Services).

Continuous Integration with VSTS -- Tips #1 pic 1

As you can see in the picture above, we have Harvest Builds.  This is to make our life much easier so that we don’t have to go to a different repo (repository) each time we want to change a build, we can do it all from one spot.  An additional thing I’ve started to do is put things under folders so it will be easier to manage.

Continuous Integration with VSTS -- Tips #1 pic 2

One of the main things I wanted to point out is that selecting the right type of project for your build is extremely important, as it will have presets for you.  In our case, we were building a Visual Studio website and deploying it to Azure’s Web App service.  From the following two screenshots you can easily tell that you might mistake picking a Visual Studio build rather than selecting the “Deployment” template of Azure WebApp.  Sadly I’ve made this mistake several times but it’s easy to see why, since Visual Studio build is what you might think you would normally pick.

Continuous Integration with VSTS -- Tips #1 pic 4Continuous Integration with VSTS -- Tips #1 pic 5Continuous Integration with VSTS -- Tips #1 pic 7

The reason is, as you can see the added MSBuild Arguments.  The only thing I configured was the subscription details of where to deploy, and the all important WebDeploy.  It’s EXTREMELY important that you specify the Virtual Application that you are running under.  For this particular example though, if it’s the root of the website, we leave it blank.  It does not need a leading slash, and if you’re deploying to the wwwroot folder no slash is needed at all, just make sure to select Web Deployment.

read more

Continuous Integration with VSTS – Tips #1

Continuous Integration with VSTS – Tips #1

Now that the blog is officially announced and released, I thought I’d show you a quick tip in how we were able to update our main website through continuous integration and continuous deployment.  Since this particular website is used just for our marketing purposes, we currently aren’t using deployment slots, so this helps us out a great deal.

All of our code is in GitHub, which I’ll blog about later.  The real issue in getting our Web App deployed took us several weeks with a ticket waiting with Microsoft support.  In the end it was worth the wait, and as you can see, with this posting it’s now live.

We’ve purposefully put all of our Builds under one Repo in VSTS (Visual Studio Team Services).

Continuous Integration with VSTS – Tips #1 pic 1

As you can see in the picture above, we have Harvest Builds.  This is to make our life much easier so that we don’t have to go to a different repo (repository) each time we want to change a build, we can do it all from one spot.  An additional thing I’ve started to do is put things under folders so it will be easier to manage.

Continuous Integration with VSTS – Tips #1 pic 2

One of the main things I wanted to point out is that selecting the right type of project for your build is extremely important, as it will have presets for you.  In our case, we were building a Visual Studio website and deploying it to Azure’s Web App service.  From the following two screenshots you can easily tell that you might mistake picking a Visual Studio build rather than selecting the “Deployment” template of Azure WebApp.  Sadly I’ve made this mistake several times but it’s easy to see why, since Visual Studio build is what you might think you would normally pick.

Continuous Integration with VSTS – Tips #1 pic 4 Continuous Integration with VSTS – Tips #1 pic 5 Continuous Integration with VSTS – Tips #1 pic 7

The reason is, as you can see the added MSBuild Arguments.  The only thing I configured was the subscription details of where to deploy, and the all important WebDeploy.  It’s EXTREMELY important that you specify the Virtual Application that you are running under.  For this particular example though, if it’s the root of the website, we leave it blank.  It does not need a leading slash, and if you’re deploying to the wwwroot folder no slash is needed at all, just make sure to select Web Deployment.

read more