difference between date rows

[jackdan]

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 ?

[Anonymous]

I want to find the calculate duration time by looking at the robot_action on the basis of robot_session (complete-start). However, I  couldn't do it.

The yield is as follows.

My aim that in Power BI:

You can use this data_sheet:

id	robot_user	robot_id	robot_action	robot_session	action_value	robot_device	status	date
14448	xxx	1	start	11577975085		EKDW10S4M04897	test	02-01-20
14447	xxx	1	Complete	11577975085		EKDW10S4M04897	init	02-01-20
14564	xxx	19	Complete	191578010500		EKDW10S4M04897	finished	03-01-20
14562	xxx	19	Data saved	191578010500	OMR_Daily-BUD-3.01.2020.xlsx	EKDW10S4M04897	running	03-01-20
14563	xxx	19	Mail sent	191578010500		EKDW10S4M04897	running	03-01-20
14561	xxx	19	start	191578010500		EKDW10S4M04897	init	03-01-20

Please help !

[mwegener]

Hi @Anonymous ,

You may download my PBIX file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

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]

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

[Anonymous]

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]

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

[Anonymous]

on the VAR line?

[jackdan]

Yes.

[v-piga-msft · Accepted solution]

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

[jackdan]

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 ? 

[Anonymous]

Looks like EARLIER (and probably the same operation done through variables) is not optimized / available in direct query https://docs.microsoft.com/it-it/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...
It does make sense as on a function like that you have to rely, on each row, on another row which might not yet have been loaded - in general in dq you don't know the entire dataset, but only the rows for the specific visual, so any filter applied will make data "partial" therefore will not make sense what you are doing.
I suggest therefore to either switch to import or do the same operation in sql before the load.

[jackdan]

Thanks for your suggestion.  I would try going through a SQL Query.

[Anonymous]

That shouldn't happen.

Make sure syntax is correct

Measurename =
VAR  varname = calculation

RETURN 
new calculation using the varname