Reply
Frequent Visitor
Posts: 6
Registered: ‎06-21-2018
Accepted Solution

difference between date rows

 

Screenshot Report PowerBI.jpg

I try to calculate the difference between row 1 and 2, 2 and 3, ....  I have created an index column but it's not possible to work with.  The field incidentid comes from a table (ActionLog) with several incident id and I have selected one..  Who can help ?


Accepted Solutions
Community Support Team
Posts: 2,674
Registered: ‎02-06-2018

Re: difference between date rows

Hi @jackdan,

 

By my tests with the calculated column formula of adetogni, everything works as expected.

 

Please check if you have installed the latest version of Power BI Desktop firstly.

 

In addition, is the fields in the table visual come from the same table (ActionLog)?

 

You could have a reference of my test file to check if I have reproduced your scenario.

 

Best  Regards,

Cherry

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

View solution in original post

Attachment

All Replies
Member
Posts: 68
Registered: ‎06-15-2018

Re: difference between date rows

Let me know if I get it correctly.

All of these rows corresponds to the same Incident. You want to calculate Time of row where rankline = 2 minus time where rankline=1?
Is this correct?


And I guess you want it to have it as a calculated column...

Frequent Visitor
Posts: 6
Registered: ‎06-21-2018

Re: difference between date rows

Yes. It's exact.  Also for the next ranklines : R3-R2, R4-R3, ... I work in Direct Query.    

Member
Posts: 68
Registered: ‎06-15-2018

Re: difference between date rows

[ Edited ]

The only way to do it is by using EARLIER. Or by defining a var.

 

On top of my head and without actually trying it live (replace YOURTABLENAME with the actual table name)

1) Create a calculated column

2) in the formula type

TimeDifference=
VAR  PreviousIncident = YOURTABLENAME[IncidentId]
VAR PreviousRank = YOURTABLENAME[RankId]-1

RETURN
   YOURTABLENAME[TimeCommentAdded] -
   SUMX(
         FILTER(
            YOURTABLENAME, YOURTABLENAME[IncidentId]=PreviousIncidentId && YOURTABLENAME[RankId]=PreviousRank)
,YOURTABLENAME[TimeCommentAdded])

The idea is that the function will run on each row. On a certain row will lookup the value of the incident and rank MINUS 1 of that row, then the return will calcualte the current time and then the SUMX will find a value of the same incidentId and rank and subtract the value.

You can achieve the same result with the EARLIER but it far more easier to understand with the variables .

 

Test it, shoudl work

Frequent Visitor
Posts: 6
Registered: ‎06-21-2018

Re: difference between date rows

If I type ActionLog[IncidentId] Power Bi doesn't recognize.  I receive "Measure 4 = actionlog[incidentid]" with a red underline.

Member
Posts: 68
Registered: ‎06-15-2018

Re: difference between date rows

on the VAR line?

Frequent Visitor
Posts: 6
Registered: ‎06-21-2018

Re: difference between date rows

Yes.

Member
Posts: 68
Registered: ‎06-15-2018

Re: difference between date rows

[ Edited ]

That shouldn't happen.

Make sure syntax is correct

Measurename =
VAR  varname = calculation

RETURN 
new calculation using the varname

Community Support Team
Posts: 2,674
Registered: ‎02-06-2018

Re: difference between date rows

Hi @jackdan,

 

By my tests with the calculated column formula of adetogni, everything works as expected.

 

Please check if you have installed the latest version of Power BI Desktop firstly.

 

In addition, is the fields in the table visual come from the same table (ActionLog)?

 

You could have a reference of my test file to check if I have reproduced your scenario.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Highlighted
Frequent Visitor
Posts: 6
Registered: ‎06-21-2018

Re: difference between date rows

I agree with you.  It works but only if we work in import mode not in direct query.  The index column isn't authorized in direct query. My question is : how to do in direct query ?