Data is offset by 1 day from the Excel data source

[Anonymous]

Good morning,

I'm having an issue with date being offset by 1 day less on PBI compared to my data source in excel,

Example is that in Excel EE A have test done on 24th, 25th and 30th. PBI is showing those tests being done on 23rd, 24th and 29th.

Date formula:

Date = ('INF - Infinity Data'[F_CRTM]/86400)+(25569)-(.20833333)-(.21875)

Does anyone know what is the paremeter to change in order to match one day?

Thanks 😃

Leo

[amitchandak]

Can you try like

Date = ('INF - Infinity Data'[F_CRTM]/86400.0)+(25569)-(.20833333)-(.21875)

Can you share sample data and sample output. that will to understand issue well

[Anonymous]

I can't share the whole file, but I put in Excel with the formulas used and if I changed them or not. The interesting thing is that the value is correct. It match perfectly, but with 1 day offset.

PBI

EXCEL

Month

Day

Time

Employee

Days/Nights

Shift

Value

Date

Time

Employee

Value

March

23

1899-12-30 04:50:54

Brett

Nights

D

265

3/30/2020

4:12:47 AM

Brett

868

March

29

1899-12-30 04:12:47

Brett

Nights

D

868

3/25/2020

4:42:44 AM

Brett

1309

March

24

1899-12-30 04:42:44

Brett

Nights

D

1309

3/24/2020

4:50:54 AM

Brett

265

March

26

1899-12-30 04:51:31

Phillip

Nights

B

833

3/27/2020

4:51:31 AM

Phillip

833

March

25

1899-12-30 05:05:25

Phillip

Days

A

2514

3/26/2020

5:05:25 AM

Phillip

2514

March

30

1899-12-30 16:20:22

Rodrigo

Days

A

554

3/26/2020

4:48:14 PM

Rodrigo

827

March

26

1899-12-30 16:48:14

Rodrigo

Days

A

827

3/25/2020

4:51:59 PM

Rodrigo

1621

March

25

1899-12-30 16:51:59

Rodrigo

Days

A

1621

Date = ('INF - Infinity Data'[F_CRTM]/86400)+(25569)-(.20833333)-(.21875) - (Not modified)

Days/Nights = if('INF - Infinity Data'[Time]< 'INF - Infinity Data'[Morning],"Nights",IF('INF - Infinity Data'[Time]>'INF - Infinity Data'[Afternoon],"Nights","Days")) - (Not Modified)

Time = TIMEVALUE('INF - Infinity Data'[Date]+.26042) - (I had to modify this formula in order to match the time btw source and PBI)

Morning = TIME(5,0,0) - (Modified in order to match shift time)

Afternoon = TIME(17,0,0) - (Modified in order to match shift time)

[Anonymous · Accepted solution]

Thanks for the support, the solution was super simple.

I justhad to add 1 day in the formula. I found the parameter related to it.

Before:

Date = ('INF - Infinity Data'[F_CRTM]/86400)+(25569)-(.20833333)-(.21875)

After adjusted:

Date = ('INF - Infinity Data'[F_CRTM]/86400)+(25570)-(.20833333)-(.21875)

Kind Regards