DAX: How to evaluate if the previous value is contained in the complete selection

[JaviMartinez93]

Hello, 

I have been thinking without being able to find a solution and I am looking for help, thank you in advance.

the context of my problem is I have two tables:

Table Costs:
Product,Cost,Quarter
1,100,2024Q1 - Cost 2025,
1,100,2024Q2 - Cost 2025,
1,100,2024Q1 - Cost 2026
....

Quarters Order Table:
Quarters,Order,
2024Q1 - Cost 2025,1,
2024Q2 - Cost 2025,2,
2024Q3 - Cost 2025,3,
2024Q1 - Cost 2026,4,
2024Q3 - Cost 2025,5

I want to make a measure that is able to calculate the difference between the present value costs and the previous one.
I achieved something:

It seems to be correct, but it does not work on the difference of the last value and this is because it is actually comparing against its previous value which I am deselecting with the filter. I mean 3643 - 3754 is not -110, it is -111 and 3347 - 3448, it is not -121 it is -101.

In short, I would like to use a combination chart whose x-axis values are controlled externally by a slicer and where there is a measure which recognizes which is the previous value that is actually within the selection.

I am very grateful for any help

[bhanu_gautam]

@JaviMartinez93 , Try following below steps first make sure there is a relationship between Costs and Quarter Order Table

Then Create a measure for Previous cost using 

Previous Cost =
VAR CurrentQuarter = SELECTEDVALUE('Costs'[Quarter])
VAR PreviousQuarterOrder = MAXX(
FILTER(
'Quarters Order Table',
'Quarters Order Table'[Order] <
MAXX(
FILTER('Quarters Order Table', 'Quarters Order Table'[Quarters] = CurrentQuarter),
'Quarters Order Table'[Order]
)
),
'Quarters Order Table'[Order]
)
VAR PreviousQuarter = MAXX(
FILTER('Quarters Order Table', 'Quarters Order Table'[Order] = PreviousQuarterOrder),
'Quarters Order Table'[Quarters]
)
RETURN
CALCULATE(
MAX('Costs'[Cost]),
'Costs'[Quarter] = PreviousQuarter
)

Then Create a Measure for Cost Difference:

Cost Difference =
VAR CurrentCost = MAX('Costs'[Cost])
VAR PreviousCost = [Previous Cost]
RETURN
IF(
ISBLANK(PreviousCost),
BLANK(),
CurrentCost - PreviousCost
)

Add a slicer to your report to control the quarters displayed on the x-axis.

Create a Combination Chart: Add a combination chart to your report and use the Quarter column from the Costs table for the x-axis. Use the Cost measure for the line values and the Cost Difference measure for the column values.

[JaviMartinez93]

Thanks for the answer but it it not working:

The relationship is done

I place the measure:

Let me remark my bbdd:
Key,Order,
2024Q1 - Cost 2025,1,
2024Q2 - Cost 2025,2,
2024Q3 - Cost 2025,3,
2024Q1 - Cost 2026,4,
2024Q3 - Cost 2025,5

I am using this Key in the x-axis of the chart to put the order I want, but it is not super important if this avoid the gold

[JaviMartinez93]

Thank you very much for your answer.
Unfortunately the solution is not giving me correct. even if I put the value on a score card or in the detail section I have no value.
The relationship is done:

I have put your suggestion with the proper changes:

Let me remark that the bbdd is
KEYforMEC:
Key,Order,
2024Q1 - Cost 2025,1,
2024Q2 - Cost 2025,2,
2024Q3 - Cost 2025,3,
2024Q1 - Cost 2026,4,
2024Q3 - Cost 2025,5

I am using this Key as well in the x-axis to get the order that I want.