topic Re: Is there a way to schedule a calculation (and freeze results) in Power BI? in DAX Commands and Tips

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

bdn008 — Tue, 11 Jun 2019 16:28:42 GMT

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.

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

parry2k — Wed, 12 Jun 2019 22:54:03 GMT

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.

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

bdn008 — Thu, 13 Jun 2019 13:00:40 GMT

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.

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

parry2k — Thu, 13 Jun 2019 18:37:26 GMT

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?

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

bdn008 — Thu, 13 Jun 2019 21:10:02 GMT

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

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

parry2k — Thu, 13 Jun 2019 21:14:09 GMT

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.

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

bdn008 — Thu, 13 Jun 2019 21:17:43 GMT

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

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

Ayelet — Thu, 22 Sep 2022 15:05:25 GMT

Hi, did you find a soulution for this?

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

Thanks!

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

bdn008 — Thu, 22 Sep 2022 16:26:15 GMT

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.