Forum Discussion
Excel Online Data source with calculated columns
Hello,
I have the following scenario.
My data source is an exce spreadsheet online (I am using it online so that I don't need a gateway).
Some of columns have formulas to give value to the cell.
An example would be If(Date<Today();1;0).
This is a very easy case, but to give to idea of a cell's value updating with time.
What I have noticed, however, is that this logic of updating with time seems not to work with automated refresh.
What I mean is that inside PowerBI, I don't get the actual value of the cell, but rather the value that the cell had when it was firstly uploaded, even though on the excel online ( the data source) the value is updated correctly.
Is this working as intended or did I miss something?
Hi gellmann94 ,
AFAIK, power bi service will convert DateTime values to UTC format instead keep the original format(local timezone). I think your scenario may be related to DateTime UTC conversion, you can check the following blog to know more about this:
Convert UTC to Local Time with Daylight Savings Support in Power BI
Regards,Xiaoxin Sheng
4 Replies
Hi gellmann94 ,
As the document said, it seems like related to your workbook and refresh settings, can you please share more about your refresh settings?
How to Get Your Question Answered Quickly
DAX TODAY function
Regards,Xiaoxin Sheng
Hello Xiaoxin, thank you for your answer.
I am not sure if I made it clear enough in my original post, but all the time logic is implemented in the online excel data source, therefore I don't understand why you linked the article about the TODAY() function in DAX.
Anyway, I am glad to provide the details about my refresh setting, if you can clarify what those are.
Do you mean how the refresh is scheduled for the Power BI Data Set in the Service?
In this case, the data is refreshed daily at midnight.I would like to add some pictures to my words, to clarify what I mean:
In the first picture you can see the offers that are included in the data model.
Please note that there aren't any offers whose data is later than 8/12/2019 (8th of December)The second picture shows what happens if I apply the same selection criteria to the data on the excel online data source.
As you can see there are plenty offers with date greater than 8/12/2019.
Finally, please note that I manually refreshed the dataset before taking these screenshots and nothing changed.Hi gellmann94 ,
AFAIK, power bi service will convert DateTime values to UTC format instead keep the original format(local timezone). I think your scenario may be related to DateTime UTC conversion, you can check the following blog to know more about this:
Convert UTC to Local Time with Daylight Savings Support in Power BI
Regards,Xiaoxin Sheng
