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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mperrot
Frequent Visitor

How can I save records before refreshing my data ?

Power BI community, I need your insights..

 

I have data coming from a CRM, they are tickets, open or closed. Everytime I refresh, some of the open tickets besome closed obviously. But in the analysis I run, some of them are directly linked to the open tickets. So at each refresh, I get the new numbers, but is there a way to store the previous record (average inactivity time on open tickets per exemple), so I can use it to see the evolution ? 

 

Numbers to make it simple : 

On Feb. 28 : 

Closed tickets : 35

Open tickets : 12

Average inactivity days on open tickets : 4,7

 

On March.1 : 

Closed tickets : 40

Open tickets : 8

Average inactivity days on open tickets : 5,3

 

The closed tickets number is irrelevant - but once the tickets have a closure date, the open tickets fall into this status.

 

Thanks per advance for your help !

1 ACCEPTED SOLUTION
stretcharm
Memorable Member
Memorable Member

Ideally you take snapshots with some ETL such as SSIS or scheduled program.

 

If this is not possibel you could do it in PowerbI using R.

 

An R transformation could snap the data and save as a file, you can then load a folder of files and use the filename to to ensure you know when it was snapped.

 

# 'dataset' holds the input data for this script

filename <- cat("C:\\temp\\myFile", format(Sys.time(), "%Y%m%d_%H%M"), ".csv", sep="")

#write.csv(dataset, file = filename )

output <- dataset

https://docs.microsoft.com/en-us/power-bi/desktop-r-in-query-editor

 

 

View solution in original post

4 REPLIES 4
nickchobotar
Skilled Sharer
Skilled Sharer

@mperrot

 

There is a data modeling concept called "Slowly Changing Dimension" . This is a data modeling technique that allows to manage current and historical data over time. You will need to have start and end timestamps of yours tickets in order to implement this approach. 

 

N  -

stretcharm
Memorable Member
Memorable Member

Ideally you take snapshots with some ETL such as SSIS or scheduled program.

 

If this is not possibel you could do it in PowerbI using R.

 

An R transformation could snap the data and save as a file, you can then load a folder of files and use the filename to to ensure you know when it was snapped.

 

# 'dataset' holds the input data for this script

filename <- cat("C:\\temp\\myFile", format(Sys.time(), "%Y%m%d_%H%M"), ".csv", sep="")

#write.csv(dataset, file = filename )

output <- dataset

https://docs.microsoft.com/en-us/power-bi/desktop-r-in-query-editor

 

 

Thanks for your answer @stretcharm, I am going to dig further on this, this looks like it could work in my case,

 

And thanks @nickchobotar, I have found some more info on this, and that is definitely an interesting approach!

Hi @mperrot,

If you think the replies are useful, please mark them as answer. So that more people will learn new things here.  If you haven't, please feel free to ask.

Thanks,
Angelia

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.