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

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

11 REPLIES 11
adetogni Senior Member
Senior Member

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

jackdan Frequent Visitor
Frequent Visitor

Re: difference between date rows

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

adetogni Senior Member
Senior Member

Re: difference between date rows

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

jackdan Frequent Visitor
Frequent Visitor

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.

adetogni Senior Member
Senior Member

Re: difference between date rows

on the VAR line?

jackdan Frequent Visitor
Frequent Visitor

Re: difference between date rows

Yes.

adetogni Senior Member
Senior Member

Re: difference between date rows

That shouldn't happen.

Make sure syntax is correct

Measurename =
VAR  varname = calculation

RETURN 
new calculation using the varname

Community Support Team
Community Support Team

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

Highlighted
jackdan Frequent Visitor
Frequent Visitor

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 ? 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 52 members 974 guests
Please welcome our newest community members: