Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculating days difference

I am using Direct Query for my Power BI report. I want to calcualte the days taken to close an opportunity, using 2 columns "Actual Close Date" and "Created On". 

I am using the following Dax Measure to calculate the difference:

 

When I use this measure on a visual I get the following error:

 

The same DAX measure works perfectly fine when I am using import query mode. 

Need Help
  • manvishah17's avatar
    manvishah17
    2 years ago

    HI Anonymous , 
    I also encountered this error sometimes , it may happen if there are blank values in columns , to troubleshoot it , you can try this code 

    Days_to_Close = 
IF(
    NOT(ISBLANK('your_table_name'[Actual Close Date])) && NOT(ISBLANK('your_table_name'[Created On])),
    DATEDIFF('your_table_name'[Created On], 'your_table_name'[Actual Close Date], DAY),
    BLANK()
)

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

2 Replies

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    HI Anonymous , 
    I also encountered this error sometimes , it may happen if there are blank values in columns , to troubleshoot it , you can try this code 

    Days_to_Close = 
IF(
    NOT(ISBLANK('your_table_name'[Actual Close Date])) && NOT(ISBLANK('your_table_name'[Created On])),
    DATEDIFF('your_table_name'[Created On], 'your_table_name'[Actual Close Date], DAY),
    BLANK()
)

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    You mentioned that this is a measure, try using MAX() or SELECTEDVALUE() to get the current value.

    Days Taken to Close a Deal =
DATEDIFF (
    MAX ( 'close opportunties'[Created On] ),
    MAX ( 'close opportunties'[Actual Close Date] ),
    DAY
)

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group