Forum Discussion
Store the data
Yes i was able to export to exel. Ok many thanks for your help but it is not a good solution form me
I agree with I_Like_Pi here. There are many ways to do this, but it all depends on how you're getting the data in the first place.
If you've only got current data from some sort of live connection, you'll need to export that data weekly to have a record of it saved somewhere, then you can compare current data to your saved past data.
If you've got all the past data already in your dataset, and you need help just returning data from the previous week, we can help with that.
If you're trying to compare what data was available in a previous week to the current data where new information has come in since, you'll need to add a new column to your data that specifies when an opportunity was added and sort by that.
I'm working with my dataset and i have created this dashboard.
The value circled by red i wuold like to store them each weeks somewhere
They are the sum of fields dataset
If you're willing to do this by hand, you can just copy/paste the values into a spreadsheet. Or when you hover over that visualization, in the top right there should be 3 dots, click those and export the data.
As far as doing this automatically, we need to know how you get your dataset, how big that dataset is, whether there are backups of that dataset or if you need to do your own backups.Super kludgey way:
Add a maintenance tab (you will need to go to this tab every week).
In this tab Add a multi row card visual. In this card drop the "Total Pipe" and "Pipe Weighed" metrics only. This card now only has 2 values... the ones you've circled.
Now click the three dot elipses for this card and say export data. When prompted browse to the folder your pbix file is in and create a new folder called "Last Week". After creating the folder save the file with the name (today is June 28th) WE_20190628.csv.
Now we are going to "get data" from More.. / Folder. Browse to and select "Last Week". Once you see the "files" (only 1 for the moment) click the edit button. Now that you are in power query click the drop down on Date created field then Date/Time Filters and finally "Is Latest". Because you currently have only 1 file it will seem odd but this is how you will want to do this. Now click the double down arrows in the content column. Your 2 values are now in that table you may need to promote header but if you built this you should be able to handle it from here. At the moment this is only this weeks data. But next week it will still be the most recent file until you go to the maintenance tab and export the data so dont export the data until you are done your weekly analysis and are prepping it for next week.
In 5 weeks when you have 5 files in that folder. Watch a youtube video on importing folders (with the file names). I think you will find it interesting. Import a new table from folder but don't select is latest. Split the date out of the file name and you have your pipe for the previous 4 weeks with all its up and down glory.
Hope this helps have a good long weekend (as in I wont be looking for your responses)
The right way would be to include an entry date in your original data extract, build a calendar table that includes a Week index, map the 2 together and then use a calculate to calculate a the pipe where Week index is equal to the current minus 1.
thanks but in my PBI file the get data command is deactivated
Well that sucks, you say get Data is greyed out. Is your original import a full model directly from SQL Server Analysis Service model SSAS. I don't use that so I am not familar but I understand that if any changes need to be made, they need to be in SSAS.
Regardless, the screen shot includes the maintenance visual I mentioned. Were you successful exporting it?
Please confirm, you right-clicked on the on the 3 dots (currrently just below the bottom corner of your 2 metrics) and exported the data?
Presuming you can do the export and really still want this. You could build a seperate PBIX file to import to. Certainly not the best solution.
Further, if you are publishing these to the online power BI perhaps you could put a tile from the 2 seperate sources into a single dash.
If this isn't workable you may need to go back to whomever built the model you are getting and discuss what data is available to build a proper roll up, that would allow you to exclude the most recent week from your analysis.
good luck.
