Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
StefanH74
Frequent Visitor

calculate average time between 2 database entries in a large database

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:

StefanH74_0-1726054395312.png

"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

4 REPLIES 4
v-tianyich-msft
Community Support
Community Support

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.

if only it was that simple....

ahadkarimi
Solution Specialist
Solution Specialist

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!

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)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors