Calculate Difference Between Rows

[MrGriff]

Hi I am trying to create a measure to calculate the difference between two rows, I had this as a calculated column but the data model is too big and crashes the service.

Resource Governing: This operation was canceled because there wasn’t enough memory to finish running it. Either increase the memory of the Premium capacity where this dataset is hosted or reduce the memory footprint of your dataset by doing things like limiting the amount of imported data. More details: consumed memory 25522 MB, memory limit 25522 MB, database size before command execution 77 MB. 

So I want to try this as a Mesure to see if this would fix the issue.

An example of the correct formate I would like is below;

Name	_VALUE	_TIMESTAMP	Correct Value
Tag 1	1588718	25/02/2021 15:49	0
Tag 1	1588718	25/02/2021 15:33	1
Tag 1	1588717	25/02/2021 15:17	0
Tag 1	1588717	25/02/2021 15:01	1
Tag 1	1588716	25/02/2021 14:45	0
Tag 1	1588716	25/02/2021 14:29	1
Tag 1	1588715	25/02/2021 14:13	1
Tag 1	1588714	25/02/2021 13:57	0
Tag 1	1588714	25/02/2021 13:41	1
Tag 1	1588713	25/02/2021 13:25	0
Tag 1	1588713	25/02/2021 13:09	1
Tag 2	1588718	25/02/2021 15:49	0
Tag 2	1588718	25/02/2021 15:33	1
Tag 2	1588717	25/02/2021 15:17	0
Tag 2	1588717	25/02/2021 15:01	1
Tag 2	1588716	25/02/2021 14:45	0
Tag 2	1588716	25/02/2021 14:29	1
Tag 2	1588715	25/02/2021 14:13	1
Tag 2	1588714	25/02/2021 13:57	0
Tag 2	1588714	25/02/2021 13:41	1
Tag 2	1588713	25/02/2021 13:25	0
Tag 2	1588713	25/02/2021 13:09	1
Tag 2	1588712	25/02/2021 12:53	0
Tag 2	1588712	25/02/2021 12:37	1
Tag 2	1588711	25/02/2021 12:21	0
Tag 2	1588711	25/02/2021 12:05	0

Any Help would be great 

[MrGriff]

Any Ideas anyone, still looking at the best way to do this?

Thanks

[Greg_Deckler]

@MrGriff Not sure what I am looking at in the table, are you looking to have a 1 on the earliest timestamp? In your calculated column, did you do something like MTBF? https://community.powerbi.com/t5/Quick-Measures-Gallery/Mean-Time-Between-Failure-MTBF/m-p/625082#M3...

Can you post your calculated column formula?

[MrGriff]

@Greg_Deckler 

Here is a sample of the data

https://1drv.ms/x/s!AjC_xz2zXa0Ug_c8jLCEyiol9CywhQ?e=ML8bGA

Active Energy Delivered - Updated = 
VAR currentname = 'Fact - AE'[_NAME]
VAR currentpower = 'Fact - AE'[_VALUE]
VAR nextpower =
CALCULATE (
MIN ( 'Fact - AE'[_VALUE] ),
FILTER ( 'Fact - AE', 'Fact - AE'[_NAME] = currentname && 'Fact - AE'[_VALUE] > currentpower )
)
RETURN 
IF ( NOT ISBLANK ( nextpower ), nextpower - currentpower )

But after I have looked at this it has give me the wrong value but this breaks the service, Fine on the Desktop...

[v-deddai1-msft · Accepted solution]

Hi @MrGriff ,

You can create an index column then use the following calculated column:

Active Energy Delivered - Updated = 
VAR currentname = 'Fact - AE'[_NAME]
VAR currentindex = 'Fact - AE'[index]
VAR nextpower =
CALCULATE (
MAX ( 'Fact - AE'[_VALUE] ),
FILTER ( 'Fact - AE', 'Fact - AE'[_NAME] = currentname && 'Fact - AE'[index] = currentindex-1 )
)
RETURN 
IF ( NOT ISBLANK ( nextpower ), nextpower - currentpower )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai