Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

calculated field is blank on web, but shows perfectly in desktop

This issue actually stems from an issue with UTC time not coming through from Salesforce properly. My workaround was to create a new column that adjusts for the time zone properly, and then assemble the date & correct time together into a new column.

This works perfectly in desktop:


CreatedDate_Final = (LeadAndOpportunities[CreatedDate_Day]) + (LeadAndOpportunities[CreatedTime])

 

but when published, that column is completely blank on web reports. CreatedDate_day is formatted from the imported "CreatedDate" from Salesforce, and CreatedTime is a date/time/timezone column that I'm just taking the "time" from.

Any idea how to get the column to not be blank on the web?

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @grodinsm,

 

Which visual did you use to display this calculated column? I used the table visual to display this column and it works fine in service.

 

Best Regards,
Qiuyun Yu

grodinsm
Frequent Visitor

Yes it's in a table, it might be an issue with one of the column format changes I did at the query level. I wouldn't have had to do any of this if the time zone was handled properly from salesforce (see my other post).

 

Roughly my calculation is this:

 

take the orignial date/time, convert it to date/time/timezone

split column to separate out the timezone piece (UTC 04:00)

split column again to get the "04" of ""04:00" 

convert the first part of the column back to date/time

use DateTimeZone.SwitchZone to make the time adjustment (for some reason 2* (the 04 column) adjusted the time properly, not just offsetting it by 4 hours)

duplicate that column, because using it as-is would use the UTC time and shove it right back to the incorrect time

take the date from one of the duplicated columns, and the time from the other, which drops the UTC Time and merge them into a new column using the formula I originally posted.

 

The reason I couldn't just offset by 4 hours in the first place is because of Daylight Savings time (sometimes it's 4 hours off, sometimes it's 5) and if it's within 5 hours of midnight, the date is incorrect. 

 

mrigau
Regular Visitor

I have the same problem but with a Web URL that points to edit a Nintex form in sharepoint online.  The first Publish works ok and looks good on the web.  Once the web tries to do a data refresh it shows the column emtpy.  Further Publish does not fix the problem.  The only fix is to delete the Dataset on the web and then perfrom another Poblish from the Desktop app.  Anyone has seen this before?

Anonymous
Not applicable

Hi

I was facing the same issue too. However, I was able to figure out a workaround. 

1. Change the datatype from Date/Time to DateTimeTimezone format.

2, Now Split apart last 6 characters of the column using split by position  

3. Then you will get two separate fields.

4. Change the datatype of primary to DateTime and the second field into the duration.

5. Now add both the fields. And this new field will have the UTC date time.

 

Hope this helps.

Thanks