cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Div Frequent Visitor
Frequent Visitor

How to create a table which lists the count for each refresh date?

I have connected a SQL database which gets refreshed every month.  I need a table which lists the count for each month. Can someone help me with this please?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: 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
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4

Re: How to create a table which lists the count for each refresh date?

Count of what? Can you specify with examples?

Div Frequent Visitor
Frequent Visitor

Re: How to create a table which lists the count for each refresh date?

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

 

 

 

Re: How to create a table which lists the count for each refresh date?

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.

Community Support Team
Community Support Team

Re: 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
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.