Difference between Rows two rows

[Anonymous]

Hi everyone!

My data is structured in Excel (displayed below). I am having translating the Excel formula in Power Bi however, and I need your help please! 

In Excel I sort the data by Key and by Date. In column D I then use this formula : IF(A2=A1,C2-C1,"")

Could you please help me translate this to PowerBi language please? 

Thank you

A	B	C	D
Key	Date	Value	Excel Formula
1	01/01/2019	500
1	01/02/2019	800	300
2	01/01/2019	200
2	01/02/2019	400	200
2	01/03/2019	900	500
2	01/04/2019	1,700	800
3	01/01/2019	600
4	01/01/2019	800
4	01/02/2019	1,000	200
5	01/01/2019	50
5	01/02/2019	80	30

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculate column using following DAX to meet your requirement, but if you have two rows contain same key and the same date, it may not work fine.

Difference = 
VAR k = [Key]
VAR d = [Date]
VAR f =
    FILTER ( 'Table', AND ( 'Table'[Key] = k, 'Table'[Date] < d ) )
VAR t =
    TOPN ( 1, f, [Date] )
RETURN
    IF ( ISBLANK ( COUNTROWS ( t ) ), BLANK (), [Value] - MAXX ( f, [Value] ) )

BTW, pbix as attached.

Best regards,

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

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Key]=EARLIER(Data[Key])&&Data[Date]<EARLIER(Data[Date])))),BLANK(),Data[Value]-LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Key]=EARLIER(Data[Key])&&Data[Date]<EARLIER(Data[Date]))),Data[Key],Data[Key]))

Hope this helps.

[Anonymous]

Many thanks to you both!

Both solutions worked great - thank you

[Ashish_Mathur]

You are welcome.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculate column using following DAX to meet your requirement, but if you have two rows contain same key and the same date, it may not work fine.

Difference = 
VAR k = [Key]
VAR d = [Date]
VAR f =
    FILTER ( 'Table', AND ( 'Table'[Key] = k, 'Table'[Date] < d ) )
VAR t =
    TOPN ( 1, f, [Date] )
RETURN
    IF ( ISBLANK ( COUNTROWS ( t ) ), BLANK (), [Value] - MAXX ( f, [Value] ) )

BTW, pbix as attached.

Best regards,

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