Calculated Column for duration between rows - Not enough memory exception

[Andy93]

Hi at all,

i have an issue calculating the duration between dates in multiple rows for a new calculated column. I get an memory exception with a 16Gb and 32Gb memory on a 64bit system. After 15 mins of calulating I get an error that I have not enough memory. There are nearly 400.000 records. I already found this article and adapt the code for my use case (https://community.powerbi.com/t5/Desktop/Help-with-Time-difference-between-multiple-rows/td-p/370952).

My Scenario:

I have a table that shows the datetime when a record attribute is changed and i want to calculate the duration between one change and an other per ticketno. Data example:

ID	Ticketno.	Date of change
1	4711	21.07.2018 15:04
2	4718	20.08.2018 18:00
3	4719	25.08.2018 18:00
3	4711	21.07.2018 17:00

ID is just an unique number The table has only 6 columns. 

I am using this Code.

Liegezeit = 
var vorgaenger =
    CALCULATE(
        FIRSTNONBLANK(F_Vorgang_Bearbeiterwechsel[date of change];1);
        ALLEXCEPT(F_Vorgang_Bearbeiterwechsel;F_Vorgang_Bearbeiterwechsel[id])
    )
var nachfolger =
    CALCULATE(
        FIRSTNONBLANK(F_Vorgang_Bearbeiterwechsel[date of change];1);
        Filter(F_Vorgang_Bearbeiterwechsel;F_Vorgang_Bearbeiterwechsel[erstellt] > vorgaenger
        )
    )
var duration = DATEDIFF(vorgaenger;nachfolger;SECOND)
//var days = ROUNDDOWN(duration/(3600*24);0)
//VAR hours = ROUNDDOWN(mod(duration;3600);0)
//VAR minutes = ROUNDDOWN(MOD(duration;3600)/60;0)
//VAR seconds = INT(MOD(duration;60))
//VAR milliseconds = ROUND(MOD(duration;1)*100;0)
RETURN duration

What can I do now? I read something about an index column? I am pretty new to Power Bi.

Greetings and thanks for your help

[Andy93 · Accepted solution]

I found a solution by using minx and earlier. Thank you to this post:

https://www.linkedin.com/pulse/mean-time-between-failure-mtbf-power-bi-greg-deckler-microsoft-mvp-/?...

Liegezeit = 
var next =
    minx(
        Filter(F_Vorgang_Bearbeiterwechsel;
            F_Vorgang_Bearbeiterwechsel[ticketno] = EARLIER(F_Vorgang_Bearbeiterwechsel[ticketno]) &&
            F_Vorgang_Bearbeiterwechsel[date of change] > EARLIER(F_Vorgang_Bearbeiterwechsel[date of change])
        );F_Vorgang_Bearbeiterwechsel[date of change]
    )
return
    if(ISBLANK(next);
        0;
        Datediff(F_Vorgang_Bearbeiterwechsel[date of change];next;SECOND)
    )

The calculation is done in seconds. Just awesome! Better work with earlier or an index instead of filtering whole dateset per line.

[v-frfei-msft]

Hi @Andy93,

I made one sample for your reference. Please check the steps as below.

1, Create the measures as below.

maxd = CALCULATE(MAX(Table1[Date of change]),ALLEXCEPT(Table1,Table1[Ticketno.]))

mind = CALCULATE(MIN(Table1[Date of change]),ALLEXCEPT(Table1,Table1[Ticketno.]))

2. Create the result measure.

Result = DATEDIFF([mind],[maxd],SECOND)

For more details, please check the pbix as attached.

Regards,

Frank

[Andy93]

HI @v-frfei-msft

Thank you for your answer. Maybe I wasn't precise enough. Your suggestion only works when you have 2 dates. The problem I have that I can have multiple ticket records with different dates. In the following screenshot is an example of the raw data. There are other tickets between one ticket. I want to calculate the duration from one date to another until there is no next record so the duration is 0.

My code works (see next screenshot), but not for the amount of data...See last column

If I am sorting by ticketno and id, I got all tickets sorted in ascending order by date but only in the query editor... I think this it could be a better solution for the performance because PowerBi only needs to look at the next record and if its the same ticketno, calculate the date. If it's not, the duration is 0. But this sorting do not affect the tables in power bi visualization and I don't know how to sort by two columns there.
I even tried a measure but it does not seem to optimize anything...

Is there maybe a better solution by using indexcolumns or anything?