DATEDIFF not working in Power BI Service

[S_Sanchez]

I have a report that uses DirectQuery to connect to an SQL Server DB.  In the report, there is a measure that uses the DATEDIFF function.  This measure will calculate correctly in Power BI Desktop.  However, once I publish the report to the Power BI Service (now connecting via the On-Premesis Data Gateway connection), the measure returns blank/null.  All other components appear to be working fine in that report, including other measures that don't use DATEDIFF.

The reason I suspect DATEDIFF is the culprit is because of its history.  Apparently it used to not be supported with DirectQuery, but eventually that was changed.  It is now considered a supported function: https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

So I suspect that when DATEDIFF and other functions were adpoted into DirectQuery's support model, the change wasn't fully implemented on Gateway connections.  I do have the latest version of the Data Gateway.  Does anyone have any other insight into this issue?  Should I go ahead and open a ticket with Microsoft about this?

[v-shex-msft]

HI @S_Sanchez ,

Can you please share your measure formula and some sample data with the same data structure for test? It is hard to troubleshoot without any detail sample data.

In addition, power bi service not has a limitation on use DATEDIFF function in measure formula. Did you double-check on that formula to confirm it can get corresponding parameters to calculate? AFAIK, it will return blank if you try to calculate between normal date value and blank value.

Sample measure=
DATEDIFF ( BLANK (), TODAY (), DAY )

Regards,

Xiaoxin Sheng

[S_Sanchez]

@v-shex-msft 

Count_LastX = SUMX(FILTER(transaction_history,DATEDIFF(transaction_history[Transaction_datetime], NOW(), MINUTE) <= Minutes[Minutes Value]),[Total Meals])

I'd share a sample .pbix file, but since we're dealing with DirectQuery, I don't think that will help much.

The Minutes table is a What-If Parameter.

This works perfectly fine in Power BI Desktop.  But returns <blank> regardles of the value of Minutes in the Power BI Service.

[v-shex-msft · Accepted solution]

Hi @S_Sanchez,

AFAIK, current power bi service does not support local DateTime, it will convert local DateTime values to UTC format. (NOW function will get local DateTime when you use on power bi desktop side, but it may get the wrong result when you calculate on service side)

If this issue is related to NOW function, you can try to use UTCNOW function to instead:

Count_LastX =
SUMX (
    FILTER (
        transaction_history,
        DATEDIFF ( transaction_history[Transaction_datetime], UTCNOW (), MINUTE ) <= Minutes[Minutes Value]
    ),
    [Total Meals]
)

Regards,

Xiaoxin Sheng