Forum Discussion
How to create a table which lists the count for each refresh date?
Hi Div,
How does your SQL table refresh? Will the new data cover the old one? In other words, the table always shows data for only one month which is the latest one.
If that is the case, what you want looks like an incremental load feature so that all history data can be stored, however this is not supported by Power BI now. Here is an idea where PG said this feature will be released in the future. As a workaround, you could export each month's data from SQL database to an Excel file, place these monthly excel files in the same folder. Then, in Power BI desktop you can get data via "Folder" connector.
While if the new updated data won't cover the older one, the new month's data is just appended to the original table, you just need to import data from SQL database and refresh it every month. To get the count value per month in a summary table, please create a calculated table with this similar DAX: (suppose there existing a date field in source table)
New Table =
SUMMARIZE (
DataTable,
DataTable[DateColumn].[Year],
DataTable[DateColumn].[Month],
"CountValue", COUNT ( DataTable[ValueColumn] )
)Best regards,Yuliana Gu
Count of what? Can you specify with examples?
Example: I have a table which lists a set of computer devices. It also shows how many are sold out as of now.
10 devices listed out of which 4 have been sold out.
Next month when the data gets refreshed, the count of computer devices sold out will increase to 6. So i need another table which lists ( kind of tracking each month's count)
Oct 2018 4
Nov 2018 6
and so on....
First, Import the table from the database into the PBI data model.
Generate an additional table withing PBI, using CALENDAR function and group it by MONTH, using SUMMARIZE. Get the required Sums from the base table that you imported from the database.
Additionally, you can provide a screenshot of how your base table in the database looks like, and I might write the dax for you.