Forum Discussion

scsi050's avatar
scsi050
Regular Visitor
6 years ago

create trending table

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...
Need Help
GilbertQ's avatar
GilbertQ
Super User
6 years ago

Hi there

 

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

scsi050's avatar
scsi050
Regular Visitor
6 years ago

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's avatar
    v-kelly-msft
    Community Support
    6 years ago

    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's avatar
      scsi050
      Regular Visitor
      6 years ago

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

       

       

      • Burningsuit's avatar
        Burningsuit
        Resident Rockstar
        6 years ago

        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