Is there a way to schedule a calculation (and freeze results) in Power BI?

[bdn008]

Hello PBI mates,

I am looking for a way to schedule a measure calculation and freeze the results into a table for that run. In other words, I already have a measure that is always running in my set BUT for monthly tracking of KPIs, for example, when I am measuring a continuely changing value over time, I want to “freeze” that value at the end of the month (for example).

Thanks.

[Ayelet]

Hi, did you find a soulution for this?

I am facing similar issue.. would be happy to know how you solved it. 

Thanks! 

[bdn008]

The short answer is - No.
I got some suggestions (elsewhere) about freezing the dataset by saving a copy. Or appending a periodic copy to the data set with additional timestamp for the data, to tell what vintage it was. However, none of that was practical for my application and it made no sense to duplicate the data periodically and increacing the size monthly by its 'current' content.

[parry2k]

@bdn008 why not you have measure that can be calculated in point of times. May be if you explain your question little more with example dataset and expected output, it will help.

[bdn008]

@parry2k, the simplest way I can explain is that I have date columns in my dataset that are not constants over time. For example if I have a 'Last-Updated' time stamp and I am refreshing my data periodically, that value on the same record will change continuously. The same with ‘Due-Date’ that could change any time. From what I know about DAX, there is no filter or trigger on a time-line. But I am willing to learn.

I need this as I have KPIs that track my metrics of compliance on a monthly basis. So in my mind, a scheduleed calculation at the end of the month would have been perfect alongside freezing those calculated values in another table for safekeeping.

[parry2k]

@bdn008 this is tough one but one with some tricks it can be achieved? How often you are going to refresh your data? How big is your data table?

[bdn008]

@parry2k, My data set is not huge but growing and is a few thousand rows right now. I am refreshing my data a few times a day and I need to (calculate and ) freeze some of my measurements at the end of each month.

I heard before some ideas dealing with cloning the dataset with frozen values, but I never liked that idea as it will grow by data set every month and will complicate my other calculations.

[parry2k]

@bdn008 it is not about taking snapshot of calculation, it is about taking snapshot of dataset before each refresh , calulation can be easily recreated if we have snapshot of data.

[bdn008]

@parry2k, I am listening and open to any idea you might be able to suggest.