Forum Discussion
Helper IIHow to Dynamically Enter Date for Subset of Data each Data Refresh
Hello, I have a data set that will increase in size bi-annually, combining historical and current records. Each record has a field stating the date on which that data was pulled from the database, the listed date depends on if that data pull was closer to the half-way mark of the year or the end of the year. EX: if subset of data was pulled on 8/4/2020, the evaluation date would be 6/30/2020. If subset of data was pulled 2/3/2021, the evaluation date would be 12/31/2020.
Below is our macro excel code to do this, but does anyone have an idea for how we can achieve this in Power BI? Power BI will need to insert the evaluation date for “new” data that is added to the dataset bi-annually, but still keeps the same evaluation dates for the “old” data.
"=IF(MONTH(TODAY())<7,DATE(YEAR(TODAY())-1,12,31),DATE(YEAR(TODAY()),7,1))"
You cannot do directly what you are asking here matsahiro. When Excel processes a macro based on how I am understanding what youa re going, it is entering a fixed amount based on that formula and it will not change. In other words, it is keying the correct date for you over the dataset for all new data, correct?
In Power BI, any formula in a custom column is recalculated each time the query is refreshed and runs over the entire dataset - it effectively deletes all data in the model then fully reloads it again. If you need the data timestamped it will have to be done at the source.
If that is not correct, please clarify exactly what is happening and what you expect to happen. If you just want a date based on a date in your table, you can use the formula mahoneypat provided, or you can replace his [SaleDate] with DateTime.Date(DateTime.LocalNow()) which is Power Query's version of Today(). But again, it will do it on the entire data set. It will not leave yesterday's data untouched. Power BI isn't built to modify and incrementally add and modify records. It does it on the entire dataset.
If you need it processed the way you want, I would suggest a few alternatives:
- Power Automate could to it for you depending on the dataset. If it is in an Excel file, it would need to be formatted a particular way for that table.
- Power Apps could also do it.
- Continue running your Excel macro daily (or whenever) to modify the data before Power BI reads it in.
6 Replies
- mahoneypat
Microsoft Employee
Here is how to write that formula for a custom column in the query editor
= if Date.Month([SaleDate])<7 then #date(Date.Year([SaleDate])-1,12,31) else #date(Date.Year([SaleDate]),7,1))
Pat
- edhans
Community Champion
You cannot do directly what you are asking here matsahiro. When Excel processes a macro based on how I am understanding what youa re going, it is entering a fixed amount based on that formula and it will not change. In other words, it is keying the correct date for you over the dataset for all new data, correct?
In Power BI, any formula in a custom column is recalculated each time the query is refreshed and runs over the entire dataset - it effectively deletes all data in the model then fully reloads it again. If you need the data timestamped it will have to be done at the source.
If that is not correct, please clarify exactly what is happening and what you expect to happen. If you just want a date based on a date in your table, you can use the formula mahoneypat provided, or you can replace his [SaleDate] with DateTime.Date(DateTime.LocalNow()) which is Power Query's version of Today(). But again, it will do it on the entire data set. It will not leave yesterday's data untouched. Power BI isn't built to modify and incrementally add and modify records. It does it on the entire dataset.
If you need it processed the way you want, I would suggest a few alternatives:
- Power Automate could to it for you depending on the dataset. If it is in an Excel file, it would need to be formatted a particular way for that table.
- Power Apps could also do it.
- Continue running your Excel macro daily (or whenever) to modify the data before Power BI reads it in.
matsahiroYou understand it correctly, where I am trying to "hard code" dates for old data and enter in new dates for new data. Thank you for the tips!
- matsahiro
If I choose to do this method in Power Apps, how would I go about that? I do not have any experience with Power Apps and would greatly appreciate any tips. Thank you very much.
- edhans
Hi matsahiro - The data would have to be in a table format. It really gets beyond the scope if this forum on how that would work. Your best bet is to search for some articles or videos on using Power Apps with Excel files and ask the excellent helpers at the Power Apps community.
In a simlar vein, Power Automate might be better here. It could run on a schedule or when the file is modified and update the data for you. Again, it must be in a table, and the Power Automate Community will be the best resource for you there.
If I were doing it, I think I would start with Power Automate first. I don't think either solution is wrong here though.FWIW, this Excel Online Action is what I think would work here, but I am no Power Automate expert, so the forum links are the best bet still.:
