Forum Discussion
Onedrive calendar table refresh
Hi, I'm having an issue with a OneDrive for Business calendar table not showing current data.
I uploaded an excel file to OneDrive with some standard date table fields, such as Year, Month, Day, Financial Year, etc and some flags such as TodayFlag, DateInPastFlag, CurrentFinancialYearFlag etc. I also have a Today() column so I can see whether the table is being updated.
I'm connecting in PBI using the web connector and using the onedrive link (removing the ?web=1 at the end of the string).
The columns with the flags aren't updating when i refresh the report, both in pbi desktop and on the portal. Is there something I'm missing? I'm sure that this was previously working ok.
here is a link to a copy of the file
Any help much appreciated 🙂
dan
Hi Anonymous
It may be due to the NOW() function in today column in the Excel file. The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously. If you store the file in Onedrive and don't open it, it will not recalculate NOW() for today column. So the data in Excel file doesn't update until next time you open it.
Instead of adding these flag columns in Excel file, you could add them with DAX in data model, or add custom columns with Power Query Editor. With these two ways, they will be recalculated and get updated in Power BI when refreshing the data. There are tutorials and scripts for both methods in this link Power BI Date or Calendar Table Best Method: DAX or Power Query? - RADACAD
Hope it helps!
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Hi, thanks for that. I suspected that was what was happening, but I thought that this had previously worked. I guess the reports with that onedrive calendar table haven't been used very much! I didn't want to do it PBI because I didn't want to have to recreate the DAX for every report. I can add the flags into a view based on a static calendar table we have and we can use that instead.
Thanks for the help,
Dan
Hi Anonymous
It may be due to the NOW() function in today column in the Excel file. The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously. If you store the file in Onedrive and don't open it, it will not recalculate NOW() for today column. So the data in Excel file doesn't update until next time you open it.
Instead of adding these flag columns in Excel file, you could add them with DAX in data model, or add custom columns with Power Query Editor. With these two ways, they will be recalculated and get updated in Power BI when refreshing the data. There are tutorials and scripts for both methods in this link Power BI Date or Calendar Table Best Method: DAX or Power Query? - RADACAD
Hope it helps!
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.