Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RichardJ
Responsive Resident
Responsive Resident

Create and update Table to show trend over time of calculated measures

Hi,

I have several measures which count rows where critieria are met. This is shown in the report as 

 

Measure Summary.JPG

It would be really helpful to store the current value of the measures in a new table that I could then use to illustrate the trend over time.

 

E.g.Trend.JPG

 

The Power BI file will be opened every day, and will have the refresh button clicked often.

 

I was wondering if it's possible that when the refresh button is clicked, as part of the refresh activity the table shown above has 'todays' numbers added to it. 

 

Or, if there is a better way of taking the values from the measures and establishing the trend over time.

 

The data source used for Power BI shows the current days 'open' items only so I can't retreive the history from the source file.

 

I could change the process for creation of the source file to include the date in the source excel filename, load all files in the directory, load all the spreadsheets into a table with a date, then use the date from the filename to build the history over time.

 

I just wondered if there is a different, more efficient approach without having to manually rename 'todays new file' to include a date.

 

Hope the question makes sense - if it's unclear please could you let me know.

 

Thanks,

Richard

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @RicharJ,

PBI in itself cannot store values of refresh even more for measures since measures are only calculated upon request so you cannot save its values on a table.

One option can be what you are refering adding the date column and saving all the files. Also may be possible to use the incremental refresh but again you need a date column and a pro license.

Last option that I see is to create a visual table an make the download of that file for a csv, so instead of saving all files with the full information just saved that specific information that you need, to what I have tried also checking maybe using power automate or power apps you can save it from the PBI directly to a sharepoint or onedrive folder.

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @RicharJ,

PBI in itself cannot store values of refresh even more for measures since measures are only calculated upon request so you cannot save its values on a table.

One option can be what you are refering adding the date column and saving all the files. Also may be possible to use the incremental refresh but again you need a date column and a pro license.

Last option that I see is to create a visual table an make the download of that file for a csv, so instead of saving all files with the full information just saved that specific information that you need, to what I have tried also checking maybe using power automate or power apps you can save it from the PBI directly to a sharepoint or onedrive folder.

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



RichardJ
Responsive Resident
Responsive Resident

@MFelix 

Thanks for clarifying Miguel and for the alternative ideas - Appreciate you taking the time to respond.

I think i'll rename 'todays' file every day and store it with the old files in a directory.

Combining these files on import will let me create the trend over time.

Cheers,

Richard

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.