Forum Discussion
create trending table
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.
Kelly
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".
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
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) ?
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
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,
KellyDid I answer your question? Mark my post as a solution!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
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,
KellyDid I answer your question? Mark my post as a solution!
