Moving date depending on time (GMT to GMT-7)

[Shard]

Hi,

So the exciting issue i have is that we download sales data from a system based in Arazona but operate in the UK, the team who want the reports are based in Arazona so want the daily / weekly sales etc based on their time but our sales agents work GMT so up to 3am.

Their reporting system (because well bad programming not my job unfortunatly) even if you are in Arazona exports the forms in GMT whcih means any sales done in the uk at 3am are represented as 3am that day not 8pm MST and so skew the results.

I'm trying to wrack my brain on how to use data transform to shift the date (and time) depending on the time in the time column so that the sales are represented for the correct date (if that makes sense), I've tried a couple of IF statments but ended up just making a bit of a mess if im honest and it was all bad, I also though "ah ill just shift my computer time to export the file" but no that didnt work either as i thought their system was using system time to export for the right timeszone but it turns out that it jsut exports in GMT even though its based in AZ and hey want the reports in MST..... so who knows whats going on with  that

Hoping some of you smarter people can assist me / point me in the right direction, a lot of the reporting forumulas all use varios iterations of the today function so ideally i want to manipulate the data in the transform editor to make life just better.

Many thankings

[dufoq3]

Hi @Shard, what about this?

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQNzDSNzIwMlHSUTIysjI0VYrVQRM2MLYyMMAUNjQCC8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Date (UTC)" = _t, #"Purchase Time" = _t]),
    #"Ad_PurchaseDateTimeUtc-7" = 
          Table.AddColumn(Source, "Purchase DateTime (UTC-7)", each DateTime.From(
               DateTime.AddZone(
                    Date.From([#"Purchase Date (UTC)"], "en-GB") & Time.From([Purchase Time], "en-GB"),
                    8
               )
          ), type datetime
     )
in
    #"Ad_PurchaseDateTimeUtc-7"

[Shard]

All sorted, after much fiddling I manged to sort it out 🙂