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

Row calculation

How to calculate rows on a table in Power BI? I want to create a column "started" as below, for each row of this column I need the result like this excel formula: =IF(AND(A3=A2;B3-B2<1);"No";"yes")

 

part

install datestarted
PAU-010126/03/2019 05:17yes
PAU-010107/04/2019 18:56No
PAU-010107/04/2019 19:56yes
PAU-010226/03/2019 05:17yes
PAU-010207/04/2019 18:56No
PAU-010207/04/2019 19:56yes
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Hi ,
Can you try to change the datediff to minutes and change the less than condition.
Thanks.

View solution in original post

Highlighted

JUST PERFECT!!!

 

started = VAR v_LagPart = CALCULATE( MAX( Data[part] ), FILTER( Data, Data[part] = EARLIER( Data[part] ) && Data[installdate] < EARLIER( Data[installdate] ) ) ) VAR v_LagDate = CALCULATE( MAX( Data[installdate] ), FILTER( Data, Data[part] = EARLIER( Data[part] ) && Data[installdate] < EARLIER( Data[installdate] ) ) ) RETURN IF( Data[part] = v_LagPart && ( DATEDIFF( v_LagDate, Data[installdate], MINUTE ) < 1440 ), "No", "Yes" )

View solution in original post

10 REPLIES 10
Highlighted
Solution Supplier
Solution Supplier

Hi,

Can you try to use this:

started = 
VAR v_LagPart = CALCULATE( MAX( Data[part] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
) 

VAR v_LagDate = CALCULATE( MAX( Data[installdate] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
)
RETURN IF( Data[part] = v_LagPart && ( DATEDIFF( Data[installdate],v_LagDate, DAY ) < 1 && DATEDIFF( Data[installdate],v_LagDate, DAY ) <> 0 ), "No", "Yes" )

result.PNG

Thanks.

Highlighted

Thank you, elegant coding.

I tried your suggestion but it does not look right, see my results table:

Capture.JPGCapture1.JPG

Highlighted

Hi,
Could you please explain the if condition. I mean what are you trying to achieve using it, specifically the date comparison part.
Thanks.
Highlighted

Hi, thanks a lot for the help so far!! 

First the table columns must be sorted, Column "Part" ascending and column "Installdate-time" also  ascending.

the column "started" intents to return a "no" if:

        the "Part" in row 2 is equal to the "Part" in row 1, and

        if the "Installdate-time" of row 2 minus "Installdate-time" of row 1 is less than 1 day.

       But, if the difference between the row 2 and row 1 "Installdate-time" is more than 1 day, it must return a "yes".

 

So for the same "Part", if it runs less than one day it did not started, but if it runs more than one day it is considered as successfully started.

 

 

Highlighted

Hi @edgarjb-68 ,

Can you try using this:

started = 
VAR v_LagPart = CALCULATE( MAX( Data[part] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
) 

VAR v_LagDate = CALCULATE( MAX( Data[installdate] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
)
RETURN IF( Data[part] = v_LagPart && ( DATEDIFF( v_LagDate, Data[installdate], DAY ) < 1 ), "No", "Yes" )

Thanks.

Highlighted

Hi!! Thanks a lot!! Almost there!!!

See that there are a few rows (highlighted) where the response should be "No", because the difference between rows 2 and 1 is less than 1 day (24 hours) for the "Installdate".

Capture.JPG

Highlighted

Hi @edgarjb-68 ,

This should work:

started = 
VAR v_LagPart = CALCULATE( MAX( Data[part] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
) 

VAR v_LagDate = CALCULATE( MAX( Data[installdate] ),
          FILTER( Data, Data[part] = EARLIER( Data[part] ) &&
          Data[installdate] < EARLIER( Data[installdate] )
          )
)
RETURN IF( Data[part] = v_LagPart && ( DATEDIFF( v_LagDate, Data[installdate], HOUR ) < 24 ), "No", "Yes" )

Thanks.

Please accept this as a solution if it satisfies the requirement. Appreciate your Kudos. 🙂

Highlighted

Wow, it is very, very close to it!!!

Though I still find only 5 instances where the response should be "No", see two examples highlighted.

One thing I noticed is that on those cases the "installdate" difference is very close to 24 hours, somthing like 23.5 or higher.

Capture.JPG

 

Highlighted

Hi ,
Can you try to change the datediff to minutes and change the less than condition.
Thanks.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors