Calculated column results in different output on Service after scheduled refresh

[akapelle]

I have a calculated column 'PaymentStatus', dax below. In Power BI desktop it gives all four statuses as output:

In Power BI service, after publishing I get the same output. But once the scheduled refresh runs it turns up this:

The same total amount of Invoices but only 2 of the 4 possible output possibilities are used. The data source is a MySQL database and the source data is not changed through all of this. 

Am I doing something wrong in my syntax here that Power BI service can't handle when refreshing data?

PaymentStatus = 
if(
    and(isblank('REP CustomerInvoices'[InvoicePaymentDate]), today() > 'REP CustomerInvoices'[InvoiceDueDate].[Date]),
    "Overdue",
    if(
        isblank('REP CustomerInvoices'[InvoicePaymentDate]),
        "Due",
        if(
            'REP CustomerInvoices'[Days Paid vs Due] <= 0,
            "InTime",
            "Late"
        )
    )
)

[akapelle]

Thanks Liu, can I solve this by using UTCTODAY function instead? 

[Anonymous]

Hi  @akapelle ,

The UTCTODAY() function returns UTC time on power bi desktop, and returns UTC time on Services.
We need to make services return the local time to be consistent. You can calculate the time difference between the local time and the services, and add and subtract through Today.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi  @akapelle  ,

It may be the calculated column you used, which is compared with today(). The time used on Desktop is displayed according to the time zone set by the computer, and the time displayed on services is the UTC displayed according to the time zone of your account, so After the scheduled refresh, Today's time will be different, resulting in comparison errors and data changes

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.