Forum Discussion
Record Data by Weekly
Hi AllanBerces
What you're trying to achieve isn't possible using Power Query, and certainly not with DAX. The reason is that you're dealing with the historical state of the data, but Power BI only works with the current snapshot of the data at the time of refresh. If any changes occur in the source, those changes will be reflected in the semantic model, effectively overwriting the previous data.
To retain historical data, it needs to be stored outside of Power BI. One option is to use Power Automate to run a query against the dataset on a scheduled basis, save the output as a CSV file in a SharePoint or OneDrive for Business folder, and then connect to that folder in Power BI as an additional data source for comparison.
Hi danextian thank you for the reply, original hours per day is my column Hours per day. Basically i want to have the copy of my previous week entire table, let say this week is WK 28, by sunday before WK 28 all column are copied from activity, date, Hours per Day, Week No. and Year coz the hours per day may change this week. and that what i want to compare WK 28 from previous week to actual current WK 28 value.
Thank you
Hi AllanBerces
What you're trying to achieve isn't possible using Power Query, and certainly not with DAX. The reason is that you're dealing with the historical state of the data, but Power BI only works with the current snapshot of the data at the time of refresh. If any changes occur in the source, those changes will be reflected in the semantic model, effectively overwriting the previous data.
To retain historical data, it needs to be stored outside of Power BI. One option is to use Power Automate to run a query against the dataset on a scheduled basis, save the output as a CSV file in a SharePoint or OneDrive for Business folder, and then connect to that folder in Power BI as an additional data source for comparison.
Hi danextian thank you very much for the info and will do you recommendation.