Saving total count per month in table for YTD.

[maartjedutchy]

Hi everybody,

So I have been researching for a while but haven't really found a working solution, hope you can help me out. I am using a SQL database that stores a month worth of data (around 500,000 records) which I use as a dataset in PBI. Every month when I refresh I lose the previous month worth of data. However my client wants month to month visualizations to show trends, e.g. incoming volume per month over the year 2018.

Now I know there are workarounds to store historicial data in PBI using R script, but I don't really need to store the whole dataset. I only want to "save" the total volume of that month that I see in my created visuals and write back in a table, so I can build up this YTD.

A current workaround I need to export the visualizations as CSV and paste the month values in an Excel sheet which I load back into PBI.  There are quite a lot of these YTD visuals in my report, so its very time consuming workaround and I really would like to automate this. 

Is there anyway to write or store these values? and not the whole related dataset?

Thanks for taking the time!

M

[v-yuta-msft · Accepted solution]

Hi maartjedutchy,

So your requirement is to export part of the dataset by PBI automatically, right? R script can save part of columns to a dataframe and then write them as a table to a .csv file or other formats.

Please refer to:https://community.powerbi.com/t5/Integrations-with-Files-and/Can-PowerQuery-write-data-back-out/td-p....

Regards,

Jimmy Tao

[maartjedutchy]

Hey Jimmy,

That could work! But I want to export data from visualizations not parts of a dataset. I guess I have to create the numbers my visualizations give me into a dataset and then use R script. 

Thanks