Forum Discussion
Record Data by Weekly
Hi good day, Can anyone pls help me, I dont know if my concern/idea is possible in PQ or Dax. I have a table and I want to record the data every week (let say every Sunday) as my original Hours Per day (Base), Basically I want to compare my original Hours Per day vs the Actual Current Hours Per Day(Actual). on a weekly basis.
DESIRE OUTPUT or Anything that will outcome the idea.
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.
5 Replies
hi AllanBerces
Please provide a sample dataset that can be easily copied and pasted into Excel. Also, could you clarify what you mean by "original hours per day"? Which specific rows in your data are you referring to? Your expected result appears to be nearly identical to your actual data, except that the previous week isn’t shown? What exactly are you trying to compare, and how should the calculation be done?
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.
Or can I copy my Hours per day column but the new copied column will not change the values when I refresh my data only it will change every Sunday refresh.
