cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
edgarjb-68 Frequent Visitor
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
vaibhavdesai Established Member
Established Member

Re: Row calculation

Hi ,
Can you try to change the datediff to minutes and change the less than condition.
Thanks.
edgarjb-68 Frequent Visitor
Frequent Visitor

Re: Row calculation

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" )

10 REPLIES 10
vaibhavdesai Established Member
Established Member

Re: Row calculation

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.

edgarjb-68 Frequent Visitor
Frequent Visitor

Re: Row calculation

Thank you, elegant coding.

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

Capture.JPGCapture1.JPG

vaibhavdesai Established Member
Established Member

Re: Row calculation

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

Re: Row calculation

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.

 

 

vaibhavdesai Established Member
Established Member

Re: Row calculation

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.

edgarjb-68 Frequent Visitor
Frequent Visitor

Re: Row calculation

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

vaibhavdesai Established Member
Established Member

Re: Row calculation

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. Smiley Happy

edgarjb-68 Frequent Visitor
Frequent Visitor

Re: Row calculation

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

 

vaibhavdesai Established Member
Established Member

Re: Row calculation

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 170 members 2,209 guests
Please welcome our newest community members: