cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LY18 Frequent Visitor
Frequent Visitor

How to use the value of the previous row same column

Hi

 

i have an issue with something i can do easily in excel but not in Pbi

Of course i wanna do it in Pbi because the real input fil is like 10 millions row and 30 columns of informations

 

I have search in the forum but could not find a case like mine

 

basically, the input is a list of event with dates and equipment name

 

 

1BCDE
2EQT NBdateEvent TextEvent ID
3EQT101/02/2019 12:43Event Text 01?
4EQT201/02/2019 12:44Event Text 02?
5EQT101/02/2019 12:45Event Text 03?
6EQT102/02/2019 12:46Event Text 04?
7EQT202/02/2019 12:47Event Text 05?
8EQT202/02/2019 13:08Event Text 06?
9EQT103/02/2019 17:08Event Text 07?
10EQT103/02/2019 17:48Event Text 08?
11EQT203/02/2019 18:02Event Text 09?
12EQT203/02/2019 19:00Event Text 10?
13EQT203/02/2019 19:00Event Text 11?

 

I need to associate an event ID based on the following rules :

for each different equipment, if an event occurs, all events in the next 60 minutes on the same equipment need to be attached to the first event ID

After 61 minutes, a new Event ID can be generated

 

here is how i do it in excel

1- I reordered the files based on the equipment NB ==> can be done easily on Pbi

2- I transform the date in a count of minutes ==> can be done easily on Pbi

3- I calculate a reference which is incremented only if the "minute count" is more than "previous reference + 60" ... if not i just recopy the previous reference ==> that is what i can not do in Pbi

4- i rename the event ID as "EQT NB" _ "Date" based on a comparison of the reference ==>  I didn't do it in Pbi but a guess a simple merge should work...

(i did recopy the formula I used in the last row to help you understand...)

 

1
HIJKLM
2EQT NBdateEvent Textdate in minutes IDreferenceEvent ID
3EQT101/02/2019 12:43Event Text 011060348310603483EQT1_01/02/2019 12:43
4EQT101/02/2019 12:45Event Text 031060348510603483EQT1_01/02/2019 12:43
5EQT102/02/2019 12:46Event Text 041060492610604926EQT1_02/02/2019 12:46
6EQT103/02/2019 17:08Event Text 071060662810606628EQT1_03/02/2019 17:08
7EQT103/02/2019 17:48Event Text 081060666810606628EQT1_03/02/2019 17:08
8EQT201/02/2019 12:44Event Text 021060348410603484EQT2_01/02/2019 12:44
9EQT202/02/2019 12:47Event Text 051060492710604927EQT2_02/02/2019 12:47
10EQT202/02/2019 13:08Event Text 061060494810604927EQT2_02/02/2019 12:47
11EQT203/02/2019 18:02Event Text 091060668210606682EQT2_03/02/2019 18:02
12EQT203/02/2019 19:00Event Text 101060674010606682EQT2_03/02/2019 18:02
13EQT203/02/2019 19:00Event Text 111060674010606682EQT2_03/02/2019 18:02
14     =IF(G13<>G12,J13,IF(J13-K12<60,K12,J13)) =IF(K13=K12,L12,G13&"_"&H13)

 

If any of you has an idea on how to do it it would be really great....

 

I'm just starting with Pbi so I definitly need some help 😉

 

thanks in advance

 

regards

14 REPLIES 14
Super User
Super User

Re: How to use the value of the previous row same column

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

LY18 Frequent Visitor
Frequent Visitor

Re: How to use the value of the previous row same column

well, i guess your answer is in the "uptime = VAR next ...." formula

even if my case is not a MTBF calculation ... maybe it can help me with my eventID

I will need to deep dive into this to understand how to use this in my case

 

too late today 😉 , i will do this tomorow morning with a fresh brain

 

i will get back to you with what i understood

 

thanks

LY18 Frequent Visitor
Frequent Visitor

Re: How to use the value of the previous row same column

Even with a fresh brain I could not find how to do what I want from your "MTBF" exemple

 

sorry, my knowledge in PowerBI and complexe formula is still "low" .... i don't get how i can "tranfer" my excel formula into PowerBI

In your exemple you use EARLIER to get the data from previous row but not from the same column (that would be "uptime") .... or maybe i didn't understand...

 

i tried to add a blank query with this formula (inspired from your post)

reference = VAR EvalReference = EARLIER([reference]) - [reference in minutes] RETURN IF(EvalReference >=60, Earlier([Reference]),[date in minutes ID] )

but didn't work 😉

 

Can you give me more details?

 

Many thanks in advance for your time

 

regards

 

 

LY18 Frequent Visitor
Frequent Visitor

Re: How to use the value of the previous row same column

No one has an idea?

Microsoft v-jiascu-msft
Microsoft

Re: How to use the value of the previous row same column

Hi @LY18,

 

You requirement here is dynamic. Actually, it's a recursion. Please download a solution from the attachment which combines Power Query and DAX. BTW, It isn't that perfect.

1. Power Query steps.

1.1 Add an index.

1.2 Calculate the minutes between the current and the previous line in the same EQT. (Add a new Column)

let lastLine = let currentIndex = [Index], currentEQT = [EQT NB] 
in Table.SelectRows(#"Added Index", each [Index] = currentIndex -1 and [EQT NB] = currentEQT)
in if Table.IsEmpty(lastLine)
then 0
else Duration.TotalMinutes([date] - lastLine{0}[date])

1.3 Add a new index which can divide them into different groups.

List.Accumulate(let currentIndex = [Index], currentEQT = [EQT NB] 
in Table.SelectRows(#"Added Custom", each [Index] <=currentIndex and [EQT NB] = currentEQT)[Minutes],
1, (seed, value) =>
if value <=60
then seed
else seed + 1)

1.4 Remove some columns. 

How-to-use-the-value-of-the-previous-row-same-column

2 Steps in the DAX.

2.1 Assign every date to a range. (THIS STEP ISN'T THAT SMART).

ranges =
VAR currentEqt = [EQT NB]
VAR currentIndex = [NewIndex]
VAR currentDate = [date]
VAR minDate =
    CALCULATE (
        MIN ( Table2[date] ),
        FILTER (
            'Table2',
            Table2[EQT NB] = currentEqt
                && Table2[NewIndex] = currentIndex
        )
    )
VAR minutes =
    DATEDIFF ( minDate, currentDate, MINUTE )
RETURN
    SWITCH (
        TRUE (),
        minutes <= 60, minDate,
        minutes <= 120, minDate + TIME ( 1, 0, 0 ),
        minutes <= 180, minDate + TIME ( 2, 0, 0 ),
        minutes <= 240, minDate + TIME ( 3, 0, 0 ),
        minutes <= 300, minDate + TIME ( 4, 0, 0 ),
        DATE ( 9999, 12, 31 )
    )
// If your data has a larger range, you have to add more branches when you see 9999-12-31.

2.2 Adjust the range to the final result.

Result =
VAR currentRanges = [ranges]
RETURN
    CALCULATE (
        MIN ( 'Table2'[date] ),
        FILTER ( 'Table2', Table2[ranges] = currentRanges )
    ) 

How-to-use-the-value-of-the-previous-row-same-column2

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft v-jiascu-msft
Microsoft

Re: How to use the value of the previous row same column

Hi @LY18 ,

 

Could you please mark the proper answers as solutions?

 

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LY18 Frequent Visitor
Frequent Visitor

Re: How to use the value of the previous row same column

sorry if i didn't answer before ... was on vacation

many thanks for the answer ... its eems very complete and detailed .... i will test it today ...

i'm not very experimented ... how do i "go into DAX" ?

Microsoft v-jiascu-msft
Microsoft

Re: How to use the value of the previous row same column

Hi @LY18 ,

 

You can download the demo from attachment in my last reply. What do you mean "go into DAX"?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LY18 Frequent Visitor
Frequent Visitor

Re: How to use the value of the previous row same column

yes i downloaded the file

i'm trying to reproduce it on my actual Database .... it's huge .... power BI edit query is taking a lot of time to calculate each "step"

 

i was mentioning DAX because there was a step that you name "Steps in the DAX." .... so i asked how to go into DAX .... i'm only manipulating the query editor of POwerBI

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,104)