Forum Discussion

S_Sanchez's avatar
S_Sanchez
Frequent Visitor
6 years ago
Solved

DATEDIFF not working in Power BI Service

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.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
S_Sanchez
Frequent Visitor
6 years ago
Anonymous 
 
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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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