Custom Column Date Fields not refreshing on PBI Cloud Service

[rhilliard]

3) Time on PBI Desktop4) Posted Time on PBI Cloud before midnight5) Refreshed Time on PBI Cloud after midnight

I originally posted an issue I was having and I was recommended to use a certain function to correct for UTC once the report is posted to the cloud service.

original issue

I then experimented with various functions and found that these date function created through custom columns would not update or refresh automatically or forced. But when I created a relative date, using a new column calculation, the date does refresh.

I believe this is a bug. These are the following steps I took:

1) New Column:

Now = Now()

UTCNow = Now() - 0.16666667

2) Edit Query - Add Column – Custom Column:

UTCToday = DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(), -4)

Today = DateTime.LocalNow()

3) Time on PBI Desktop (see images)

4) Posted Time on PBI Cloud before midnight

5) Refreshed Time on PBI Cloud after midnight

[v-haibl-msft]

@rhilliard

I can repro the same issue as you. I’ve reported it internally to Power BI Team: CRI 49165956
I’ll post here once I get any update about it.

Best Regards,
Herbert

[v-haibl-msft]

@rhilliard

I’ve got response from the Product Team.

This is a known and long-standing issue; Using DateTime.LocalNow() or any other function that depends on the current time will not work correctly in Direct Query. There's work being done to overcome the problem but it likely won't be complete until well into 2018. The workaround is to do any time operations in the DAX layer.

Best Regards,
Herbert

[rhilliard]

@v-haibl-msft

Thanks for the response...I wish the news was more positive.

When you say the DAX layer, are you referring to calculated columns and measures outside of query editor? Or something else?

What would be an example of a DAX query using the function to correct for UTC +4 when posting?

[Anonymous]

Any news on this? The issue I've noticed with Power Query/M Custom Columns (which may be related) is that filtering on them does not reduce the overhead of the data load to the report.

The behavior I see is as follows:

Scenario 1: Filtering on a column in the original dataset

1. Data table has 16m rows, 8m where year = 2018, 8m where year = 2017 (year is a column in the original dataset);

2. Apply filter year = 2018 and load the data (i.e., Close & Apply). Only 8m rows will be available in the report and you also can see that only 8m rows have loaded, i.e., when you watch the number creep up during load, it stops at 8m (and takes half the time).

Scenario 2: Filtering on a custom column

1. Same data table as in Scenario 1;

2. But add custom column (Is2018) which returns TRUE for 2018, FALSE for any other year;

3. Apply filter Is2018 = TRUE and load the data. Only 8m rows are available in the report BUT when you watch the number creep up during load, it does not stop until 16m.

So it looks like the Power Query filtering on custom cols works in terms of limiting the data "available" in the report, but doesn't give you any savings in overhead or load time. Would love to learn more about what's going on here!

[Anonymous]

I should add that the custom cols I'm using involve DateTime.LocalNow() and I think there may have been issues with this, but I've tested this in a smaller, very simple dataset using a simple boolean filter and the same issue occurs, so the issue is not caused by the specific custom columns I'm filtering on.