Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data is offset by 1 day from the Excel data source

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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       
      MonthDayTimeEmployeeDays/NightsShiftValue DateTimeEmployeeValue       
      March231899-12-30 04:50:54Brett NightsD265 3/30/20204:12:47 AMBrett 868       
      March291899-12-30 04:12:47Brett NightsD868 3/25/20204:42:44 AMBrett 1309       
      March241899-12-30 04:42:44Brett NightsD1309 3/24/20204:50:54 AMBrett 265       
      March261899-12-30 04:51:31Phillip NightsB833 3/27/20204:51:31 AMPhillip 833       
      March251899-12-30 05:05:25Phillip DaysA2514 3/26/20205:05:25 AMPhillip 2514       
      March301899-12-30 16:20:22Rodrigo DaysA554 3/26/20204:48:14 PMRodrigo 827       
      March261899-12-30 16:48:14RodrigoDaysA827 3/25/20204:51:59 PMRodrigo1621       
      March251899-12-30 16:51:59RodrigoDaysA1621            
                         
      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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