topic Re: calculate average time between 2 database entries in a large database in DAX Commands and Tips

calculate average time between 2 database entries in a large database

StefanH74 — Wed, 11 Sep 2024 12:07:54 GMT

Hello,

I'm trying to create a report where we can see the average cycle time of our products.

every time a cycle is completed on a machine (we have 30), a entry is made in the database.

so I need to compare the entry time with the previous entry (for that product) to get the time for that cycle, and then show in my report the average cycletime per product.

data looks like this:

"Cyclustijd" is a calculated column to get the time (in seconds) between the entry and the previous one.

Cyclustijd = VAR order_ = vHardwareLog[orderNumber] VAR created_ = vHardwareLog[created] VAR created_previous_entry = CALCULATE( MAX(vHardwareLog[created]), FILTER( ALL(vHardwareLog), order_ = vHardwareLog[orderNumber] && created_ > vHardwareLog[created] ) ) VAR cyclustijd_ = (created_ - created_previous_entry)*24*60*60 RETURN IF( cyclustijd_ > 300, BLANK(), cyclustijd_ )

and as you can see in the example above, it calculates just fine.

And in my report I can show the average cycle-time.

The problem is that I can only run this with about a month of data (±270.000 database-entries)

anything bigger than that, and I get an error: "Not enough memory"

and I would really need it to run with at least a year's worth of data!

so if anyone knows a solution for this problem,

or can think of another way to get the result I need, I would greatly appreciate that.

Thanx,

Stefan

Re: calculate average time between 2 database entries in a large database

ahadkarimi — Wed, 11 Sep 2024 17:20:38 GMT

Hi StefanH74, give this a try, and if you encounter any issues, let me know.

Here’s a modified version of your DAX, optimizing memory usage:

Cyclustijd = VAR order_ = vHardwareLog[orderNumber] VAR created_ = vHardwareLog[created] VAR created_previous_entry = CALCULATE( MAX(vHardwareLog[created]), REMOVEFILTERS(vHardwareLog), vHardwareLog[orderNumber] = order_ && vHardwareLog[created] < created_ ) VAR cyclustijd_ = (created_ - created_previous_entry) * 86400 RETURN IF(cyclustijd_ <= 300, cyclustijd_)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!

Re: calculate average time between 2 database entries in a large database

Anonymous — Thu, 12 Sep 2024 02:03:52 GMT

Hi StefanH74 ,

You can try changing the calculated column to a metric, which usually reduces memory consumption:

Cyclustijd = VAR order_ = SELECTEDVALUE(vHardwareLog[orderNumber]) VAR created_ = SELECTEDVALUE(vHardwareLog[created]) VAR created_previous_entry = CALCULATE( MAX(vHardwareLog[created]), FILTER( ALL(vHardwareLog), vHardwareLog[orderNumber] = order_ && vHardwareLog[created] < created_ ) ) VAR cyclustijd_ = (created_ - created_previous_entry) * 24 * 60 * 60 RETURN IF( cyclustijd_ > 300, BLANK(), cyclustijd_ )

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: calculate average time between 2 database entries in a large database

StefanH74 — Thu, 12 Sep 2024 06:37:50 GMT

Hello ahadkarimi ,

thanx for the effort, unfortunately this makes no difference. cannot even do 2 months of data.

I think I will need a completely different approach to this problem, (even though I do not see how)

Re: calculate average time between 2 database entries in a large database

StefanH74 — Thu, 12 Sep 2024 06:38:33 GMT

if only it was that simple....