Calculate Row Difference between two consecutive rows based on two consecutive rows & other columns

[Amir851]

Dear All,

I am trying to calculate row diff between two consecutive rows of Running Total % (which is Measure) based on two consecutive Rank All Machine (which is also Measure). Rank all Machine is being calculated based on the Sum of duration.

There are also four slicers named Machine, Machine Type, year & week available in the report and Component, Sum of Duration, Machine, Machine Type, Year & week are part of Table named Downtime.

Row Diff of Running Total % should be calculated based on two consecutive Ranks(Rank 3 row - Rank 2 row & Rank 6 row - Rank 5 row)

I have tried below dax but it is not working and tr returns an empty value.

Row diff =
VAR NEXT =[Rank All Machine] +1
return
CALCULATE([Running Total %],FILTER(Downtime,Downtime[Running Total %]=NEXT))

@Phil_Seamark @v-shex-msft  Can you please help me with this request.

Component (Table column)	Sum of Duration (Table Column)	Running Total %(Measure)	Rank All Machine(Measure)	Row Diff(Need New Measures)
Component 9	4.85	40,10	1	0,00
component 3	2.10	57,40	2	17,30		slicer 1-Machine
component 2	1.37	68,70	3	11,30
Component 4	1.35	79,90	4	11,20		slicer 2- Machine type
component 6	0.70	85,60	5	5,70
component 5	0.63	90,80	6	5,20		slicer 3- Year
Component 8	0.49	94,90	7	4,10
component 12	0.45	98,70	8	3,80		slicer 4- week
Component 7	0.12	99,70	9	1,00
Component 10	0.02	99,80	10	0,10

[v-lionel-msft]

Hi @Amir851 ,

Maybe you can do like this:

rows_diff = 
CALCULATE(
    SUM([R_total]),
    FILTER(
        'Sales (3)',
        [Run_all] = EARLIER([Run_all]) + 1
    )
)

But, pay attention, my [R_total], [Run_all] , [rows_diff] are all calculated column.

I tried to do it by measures, but don't get it.

Hope the result is what you want.

Best regards,
Lionel Chen

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

[Amir851]

Dear @v-lionel-msft ,

Thank you for your response.

Actually , I know how to implement the above scenario with calculated columns. But in my current requirement all the columns except description are measures. I want to implement the above scenario using measures only.

Can you please let me know whether is there any possibilities to implement the above scenario by considering all those columns as measures.

Thanks

Amir 

[v-lionel-msft]

Hi @Amir851 ,

I did initially plan to use measure to do this, but I found it impossible.
Measure and column are not  arbitrary. They have different return values, so they use different functions.
Here's an article about measure and column:
https://docs.microsoft.com/en-us/dax/dax-overview 

Best regargs,
Lionel Chen