cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Satch
Helper III
Helper III

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 6
KimKnudsen
Frequent Visitor

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

@KimKnudsen

 

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...

KimKnudsen
Frequent Visitor

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 

Thanks Kim,

 

I will look to this setup later when I have more time and knowledge

For now I use the one mentioned. It's quick an dirty 🙂

v-jiascu-msft
Microsoft
Microsoft

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.Create separate table with Inventamount per month.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!