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
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.
Yes i was able to export to exel. Ok many thanks for your help but it is not a good solution form me