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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
edgarjb-68
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
Anonymous
Not applicable

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

View solution in original post

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
Anonymous
Not applicable

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.

Thank you, elegant coding.

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

Capture.JPGCapture1.JPG

Anonymous
Not applicable

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

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.

 

 

Anonymous
Not applicable

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.

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

Anonymous
Not applicable

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. 🙂

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

 

Anonymous
Not applicable

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.