How can I save records before refreshing my data ?

[mperrot]

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 !

[nickchobotar]

@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 · Accepted solution]

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

[mperrot]

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!

[v-huizhn-msft]

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