Hello. I've been using Power BI for about a year and have hit a conceptual hurdle.
I've been tasked to automate a monthly KPI dashboard. This was previously a manually counted and maintained KPI. This KPI measures the number of database items by category over time (monthly). I was handed over 2 years of historical data to combine with the live data. It is a basic stacked column visual.
I've been successful so far in creating the queries to connect the historical data to counts of the live data. What remains is the counting and continuing appendation at the end of each month. I.e. I have the 2 years of historical data to Nov'20, I have an item in the axis that is titled "Current" and is grouped like the historical data (counts by category); now, I need to fill the gap between Nov'20 and "Current" as time goes forward. I could simply dump the "Current" values into a spreadsheet or folder at the end of every month, but I am most interested in seeing if it is possible to automate this portion of the task. Is it possible to write a query that can measure datetime info and append at a certain time? Should I use report builder instead to build the data?
