Wrong date based on a timefilter in PowerQuery and SQL Data base

[RZ89]

Hi everyone,

I have a very strange behavior in an important report and cannot solve the issue.

The reports is caltulating values based on demands in the past regarding current date.

Example:

01.06. - 1 piece

02.06. - 1 piece

03.06. - 1 piece

04.06. - 1 piece

05.06. - 1 piece

06.06. - 1 piece

07.06. - 1 piece

08.06. - 1 piece

Today 08.06

My Report gives me as sum before 07:00 am 6 piece.

When the report is running after 07:00 am is says 7 piece.

So the report thinks it is the 07.06 before 7 am.

The sourcetable of data is basicially the same and has all past and future data in it. I checked that. The difference with the result first shows up with my filtering regarding date.

The report runs in German Time zone, so I think it has to be something with the timedifference of 7 hours betweens DE and US Time. But how can i fix that?

I tried two types of filtering to solve that problem.

1. Set up a calucated column that give me my exakt time and date in DE timezone --> not working
2. Tried to filter the date column; example: Table.SelectRows(#"Plant LX01", each [End Date] >= Date.From(DateTime.LocalNow())) --> not working too with DateTime.UtcNow()

I set up a table to check which date is avaiblable in the table after filtering, see picture

I hope my issue can be unterstood and somebody can help me!

Thanks 🙂

[jennratten]

Hello - have you tried converting both dates before comparing?  In this example, I used variables to create the local and requirement date and added 10 hours to each so that the requirement date would initially appear as tomorrow.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    // Number of hours to force German time into tomorrow, just for this example.
    varOffsetHours = 10,
    // Local datetime + offset hours
    varLocalDateTimeZone = DateTimeZone.ToLocal(DateTimeZone.FixedLocalNow() + #duration(0,varOffsetHours,0,0)),
    // Requirement datetime + offset hours
    varRequirementDateTimeZone = DateTimeZone.SwitchZone(DateTimeZone.UtcNow() + #duration(0,varOffsetHours,0,0),2,00),
    // Get your local time. 
    LocalDateTimeZone = Table.AddColumn(Source, "LocalDateTimeZone", each varLocalDateTimeZone, type datetimezone),
    RequirementDateTimeZone = Table.AddColumn(LocalDateTimeZone, "RequirementDateTimeZone", each varRequirementDateTimeZone, type datetimezone),
    LocalDate = Table.AddColumn(RequirementDateTimeZone, "LocalDate", each Date.From([LocalDateTimeZone]), type date),
    RequirementDateToLocalZone = Table.AddColumn(LocalDate, "RequirementDateInLocalZone", each Date.From(DateTimeZone.ToLocal([RequirementDateTimeZone])), type date)
in
    RequirementDateToLocalZone

[RZ89]

Hi Jennratten,

thank you for your help. I tried to set up a column with localtime+Date and derivate it to a column with only the date. With adjustion of 2 hours before it seems to work perfect.

Thanks!!

[RZ89]

Hi, I tested it last week but I still got the same behavior on getting the datas based on that filter.

Here the code:

let
Quelle = SapHana.Database("phnhdb.dx.deere.com:30015", [Implementation="2.0", Query="select ""MATERIAL"" as ""Material"",#(lf) ""PLANT"" as ""PLANT"",#(lf) ""REQUIREMENT_DATE"" as ""Requirement Date"",#(lf) ""REQUIREMENT_QUANTITY"" as ""Requirement Quantity"",#(lf) ""REQUIREMENT_TYPE"" as ""Requirement Type"",#(lf) ""RESERVATION"" as ""Reservation"",#(lf) ""STORAGE_LOCATION"" as ""Storage Location""#(lf)from ""_SYS_BIC"".""deere.mai.adhoc.base/CA_MD04_RESB""('PLACEHOLDER'=('$$IP_PLANT$$','LX01'))#(lf)where (""REQUIREMENT_TYPE"" = N'SB' and ""REQUIREMENT_TYPE"" is not null) and (""STORAGE_LOCATION"" = N'ASB' and ""STORAGE_LOCATION"" is not null) and (TO_TIMESTAMP(""REQUIREMENT_DATE"") >= ADD_DAYS(CURRENT_UTCDATE,-90) and TO_TIMESTAMP(""REQUIREMENT_DATE"") < ADD_DAYS(CURRENT_UTCDATE,+30))#(lf)group by ""MATERIAL"",#(lf) ""REQUIREMENT_DATE"",#(lf) ""REQUIREMENT_QUANTITY"",#(lf) ""REQUIREMENT_TYPE"",#(lf) ""RESERVATION"",#(lf) ""PLANT"",#(lf) ""STORAGE_LOCATION""#(lf)"]),
#"Gefilterte Zeilen - vor Heute" = Table.SelectRows(Quelle, each [Requirement Date] < Date.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),2,00)))
in
#"Gefilterte Zeilen - vor Heute"

[Vijay_A_Verma]

This is a common issue as Power BI service takes UTC datetime as current datetime. So, wherever you are using datetime, you will have to add time difference between UTC and German. I notice that  there is +2.00 hours of difference between UTC and Germany. So, you can use following construct where you are using current date/time. (Please see whether it is 2 hours of difference or 7 hours)

= DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),2,00)

 Note - This approach will work only for people in German time zone. Hence, if there are multiple timezones, then the above approach won't work and it is better to leave at UTCNOW only.