cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dregan
Helper I
Helper I

Time Zone Issues with PowerBI Integration, PowerApps and Sharepoint

Hi Team - 

 

I am using Power BI Integration in a Power Apps to reference data from the Power BI and then using that data to patch to a sharepoint list.  It works perfectly for users in my time zone (EST) but when a user in a different time zone uses it, the data doesn't Lookup in the Sharepoint list properly due to the times being off.  I really only care about the dates matching, but everything I have tried doesn't work. 

 

In my Power BI - I have the first of every month as the date.  So the user will select a date and it will be something like 9/1/2021.  Of course, it has to throw a time on there, so it shows midnight.  When those records are references in PowerApps, the time shows as: 2021-09-01T04:00:00.000Z indicating that it is seeing that the Power BI is in EST.  Ideally - I would like to convert my Power BI to UTC, but I haven't found a way to do this. 

 

Next thing I've looked into is converting that DateTime to UTC in PowerApps by using:  DateAdd(Gallery6.Selected.'Month Start Date', - TimeZoneOffset(),Minutes) but for some reason it always just results in a Blank.  The TimeZoneOffset works perfectly on its own and results in 240, so I know that's right.  And PowerApps recognizes the 'Month Start Date' as a Date, so I don't see why it isn't working.

 

I have also tried converting pulling the dates out of the string and converting to text, but that hasn't worked either. ( DateValue(Text(Gallery6.Selected.'Month Start Date')) .  I have changed my Sharepoint list to UTC, and that is now showing 04:00 for all of the times.  

 

I'm not sure what to try next! It seems like getting Power BI to UTC would solve all of my problems, but I don't even know if that's possible.  Thanks in advance for the help!

5 REPLIES 5
StalinPonnusamy
Community Champion
Community Champion

Hi @dregan 

 

Can you try this. We will get a delegation warning. We can ignore the warning if the data size is less than the settings

With (
    {
        _ExistingRecord: LookUp(
            SPList,
            Text(
                'Date reported',
                DateTimeFormat.ShortDate
            ) = Text(
                DateValue1.SelectedDate,
                DateTimeFormat.ShortDate
            )
        )
    },
    Patch(
        SPList,
        If(
            IsEmpty(_ExistingRecord),
            Defaults(SPList),
            _ExistingRecord
        ),
        {Title: DataCardValue9.Text}
    )
)

 

dregan
Helper I
Helper I

I tested this with a Label -- I used the formula you mentioned.  You can see that it shows it's at 12:00 AM.  But the result of this formula is still showing the TZ. 

dregan_0-1631017597800.png

dregan_1-1631017661556.png

 

 

StalinPonnusamy
Community Champion
Community Champion

Hi @dregan

 

The short date should not be having a time zone. Can you check this

 

Text(
    Gallery6.Selected.'Month Start Date',
    "mm/dd/yyyy"
)

 

Very strange - this isn't working either:

dregan_0-1631020584533.png

 

I was able to solve this problem in a very round about way. I have year, month, and day columns in my date table.  I pulled them into the PBI Integration and used the Date(Year, Month, Day) function.  From there I was able to use DateAdd(Date, -TimeZoneOffset(),Minutes).

 

This is working - but it is still very puzzling why the date field did not work.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,334)