Forum Discussion
Create separate table with Inventamount per month
Hi,
I want to build historical data from my import.
Will explain:
Every day I get the data from an xml file, it's something like
ITEM QTY COSTPRICE
Item A 2 10
Item B 5 25
The thing is, it is only the current inventory, there's no historydata.
So what I'm looking for is at every refresh, PBI creates a record with total inventamount in a separate table per month, but only do this per month.
So today: total inventamount is 145
>>record in separate table
Month Amount
September 26 145
Tommorrow f.e. Inventamount is 155
Overwrite the september 26. so table lookslike:
Month Amount
September 27 155
and so on.
Next month
Oktober 1st tasble looks like:
Month Amount
September 30 115
Oktober 1 135
So in the end I have 1 amount per month, per the last date in the month the data was refreshed:
Month Amount
September 30 115
Oktober 31 145
November 30 165
December 31 185
Any guidance is appreciated
6 Replies
Hi Satch,
I found a solution here: Storing-and-using-information-from-a-dynamic-data-source-using. You can have a try. I think this is almost impossible with Power BI Desktop only. I can make some explanation.
1. Power BI aims at data analysis and visualization.
2. If we start doing this, we need to get date and total amount. There is no dates. So a possible try is functions like TODAY() and NOW(). But the functions are dynamic. For example, we opened the file yesterday, now() is yesterday. And we open it today, the now() surely return today. So there is no way to keep historical data.
Every refreshing or opening up will lead update.
You can submit or vote an idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas?query=historical
Best Regards!
Dale
Thanks,
I was looking for something simple in Power BI.But as you said, it seems impossible to do that.
I have found a way to get my graphs, by exporting once per month a Table visualisation with the total Amount and date to a file.
It involves 1 manual action, but it's easy and stable.
Hi Satch,
Maybe this isn't the answer you are looking for, but if the end goal is to create a historical inventory report with Power BI, why not just save the xml file as your are doing dayli, and make Power BI crawl through the files to create the table you are looking for?
You can use Power Query to extract the data from all of your files and apply the logic your are talking about, saving the latest value each past month and the current value in the current month. That gives you the ability to create a historical inventory report.
You might consider using Excel and Power Query combined with Power Update, to create a consolidated Excel Table to query from Power BI Desktop.
Hope it's helpfull.
Best Regards,
Kim
Hi Kim,
thaks.
What I do now is export the data from the table visual in a separate file each month.
And power BI reads and combines those different files in 1 table with the values per month.
Are we talking the same? Or do you have another idea in your reply.
I don't know anything about Power Query combined with Power Update...
Hi Satch,
I'm not sure I understand why you would export the data from a table visual, and then import it again. I would try using the raw export files, and then build my table from them.
Power Update and Power Query combined shouldn't be neccesary. Power Update is a tool for scheduling refresh, and if you then used Power Query in Excel to update an Excel table in advance, you might have a better setup. Anyway, that's probably to much, compared to what you are after..
/Kim
