create trending table

[scsi050]

I am trying to find a way to create trending data within PowerBI.   I have table2 that gets updated with each refresh and upon each refresh, I would like a new row to be appended to an existing table.  The table would have the current date/time in the first row and a count of the number of rows from table2.

Then with that table of trending data I would graph it.

Is this possible? thanks

[GilbertQ]

Hi there

Could you please give some sample data and then also an expected result to assist?

[scsi050]

Example of table2:

Column1,column2

Abc,123

Cde,456

Trending table:

Date,count

7/29/2020,<count of rows in table2 as of 7/29 refresh>

7/30/2020,<count of rows in table2 for today's refresh>

...and so on

Then I can visually show a graph of how the count of table2 rows differs as time goes on.

[v-kelly-msft]

Hi  @scsi050 ，

Create 2 measures as below:

DATE/time = NOW()

Count = COUNT('Table'[Column1])

Then you will see：

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[scsi050]

Hi, 

1) Does this append to the same table by adding a new row upon every refresh?

2)  I am running Power BI Desktop v2.83.5894.881 64-bit (July 2020) which looks like the latest version, but when I open your pbix file, I get the error:

"create trending table.pbix is incompatible with your current version of Microsoft Power BI Desktop. Please install the latest version and try opening the document again".

[v-kelly-msft]

Hi  @scsi050 ,

Your version is not the latest one,pls update to the version below:

Version: 2.84.282.0 64-bit (August 2020)

As your result needs to be updated due to every refresh, so measure is the best choice,and when you put the measures in a table visual,it will be added as 1 row in table.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Burningsuit]

Hi @v-kelly-msft 

It's 3rd August 2020,  Version: 2.84.282.0 64-bit (August 2020) has not been released yet and is not available for public download. Most people still have the Version: 2.83.5894.961 64-bit (July 2020), only MVP's, Advisors and Microsoft staff have access to the August version at this time.

Even with that issue, I'm sure your solution will not do what was requested.

When you refresh the report, the measure  DATE/time will get updated and overwritten with the value in NOW(). The measure Count will get updated and overwritten with the value from COUNT('Table'[Column1]).

Then you will see..

DATE/time                               Count

03/08/2020 09:35:17                        2

In the Table visualisation

What you will NOT see is what was requested which would be...

DATE/time                               Count

31/07/2020 01:42:43 AM                 2

03/08/2020 09:35:17 AM                 2

As I explained, I don't think there is a way for Power BI to WRITE the data required to generate a Trending Table as requested, you'll have to use SQL or a PowerApp, or Flow.

I hope this clears things up

Stuart

[v-kelly-msft]

Hi  @scsi050 ,

Yes, sorry,as corrected by @Burningsuit ,we can only write the current data after refresh,but once you did it,it will overwrite the previous one, a Trending Table is not available as you requested in power bi .

Sorry again.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[scsi050]

thanks for the responses.

[scsi050]

One more related question. If the data cannot be generated within powerbi, I could do it external to powerbi.

1) is there an API  endpoint to download a table from powerbi? I see that there are but from what I can see, they all require PowerBI Premium (I just have Pro). Is that true?

2) If #1 is possible, is there an API endpoint to update a table? (And again, same question about powerbi licensing).

[Burningsuit]

Hi @scsi050 ,

The response from @v-kelly-msft will only show the last date and time the data was updated and the row count at that time. It will only ever show 1 set of data.

As you suspected it does not build a list of times and row counts that you can use as a trending table.

This sort of thing is difficult in Power BI as it is primarilly a reporting system, and there is no easy way to write back the data it reads. DAX has no "update table" function as such. You would be better looking at doing this with another tool, maybe a PowerApp, or SQL query.

Oh, and by the way, I get the same error message trying to load the pbix file they provided, I too am on the July release. 

Hope this helps

Stuart  

[scsi050]

That's unfortunate.  If I process the trending calculations externally from PowerBI, does it have an API endpoint that would allow me to get a table's data, I can count that, and then POST to a Power BI API endpoint to add a new row to a table (add a new row, not overwrite one) ?

[Burningsuit]

I don't think so. Again Power BI has no tools for writing data. I'd be looking at Power Auomate and an SQL query to do this sort of thing.

Stuart