calculate average time between 2 database entries in a large database

[StefanH74]

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

[Anonymous]

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.

[StefanH74]

if only it was that simple....

[ahadkarimi]

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!

[StefanH74]

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)