DAX to find the change between two numbers at the start and end of a period

[DebbieE]

Say Im looking at a months worth of figures

02/06/2023     96

06/06/2023     95

10/06/2023     96

20/06/2023    102

Change  = [Max Amount]-[Min Amount] gives me 7 because from 95 to 102 there is a change of 7 which is great. (95 to 102)

But I also want to see the change between the first and last which is 6 (96 to 102)

Ive been doing a bit of googling to see if I can get these but havent have any luck so far and wondered if anyone might have an idea for this?

I have a date dimension in this schema (Dim Date) Containing the date (Date)

[DebbieE]

cant get this working. Any help would be really appreciated. Bearing in mind my date table has a kay and date. and the fact table has the date key in it. The dates arent in the fact table. 

[123abc]

I apologize for any confusion. If your date table ("Dim Date") contains a key and date, and your fact table has a date key but doesn't contain the actual dates, you can still calculate the change between the first and last values within a selected period. Here's how you can do it:

1. Create a measure to calculate the first and last values within the selected period:

FirstValue =

VAR MinDateKey = MIN('Dim Date'[DateKey])

VAR MaxDateKey = MAX('Dim Date'[DateKey])

RETURN

CALCULATE( MAX('Fact Data'[Value]),

FILTER(

'Fact Data',

'Fact Data'[DimDateKey] = MinDateKey || 'Fact Data'[DimDateKey] = MaxDateKey

)

)

This measure calculates the first and last values based on the minimum and maximum date keys within the selected period.

1. Create a measure to calculate the change between the first and last values:

ChangeBetweenFirstAndLast = [LastValue] - [FirstValue]

This measure calculates the difference between the first and last values.

Now, when you use the "ChangeBetweenFirstAndLast" measure in your visualizations, it should show you the change between the first and last values within the selected period.

Make sure that your relationships between the date key in your fact table ("DimDateKey") and the date key in your date dimension ("DateKey") are correctly set up in your data model. The date keys in both tables should be linked for these calculations to work correctly.

[DebbieE]

FirstValue =

VAR MinDateKey = MIN('Dim Date'[DateKey])

VAR MaxDateKey = MAX('Dim Date'[DateKey])

RETURN

CALCULATE( MAX('Fact Data'[Value]),

FILTER(

'Fact Data',

'Fact Data'[DimDateKey] = MinDateKey || 'Fact Data'[DimDateKey] = MaxDateKey

)

)

Im a little bit confused on this part/ if this is the First Value whey does it have the Max Value in it? 

also there is only one DAX there so where is Last Value?

[123abc]

To calculate the change between the first and last numbers within a specified period in DAX, you can use the following approach. Assuming you have a Date table (Dim Date) and a Fact table containing your data with columns for Date and Amount, you can create a calculated column or measure to achieve this.

First, create a measure that calculates the maximum and minimum values within the selected period:

Max Amount = MAX('Fact Table'[Amount]) Min Amount = MIN('Fact Table'[Amount])

Next, create a measure to calculate the change between the first and last dates in your selected period:

First Date = MIN('Fact Table'[Date]) Last Date = MAX('Fact Table'[Date]) Change Between First and Last = VAR FirstValue = CALCULATE([Max Amount], FILTER('Fact Table', 'Fact Table'[Date] = [First Date])) VAR LastValue = CALCULATE([Max Amount], FILTER('Fact Table', 'Fact Table'[Date] = [Last Date])) RETURN LastValue - FirstValue

In this measure, we first calculate the maximum value (Max Amount) within the entire period, and then we use the CALCULATE and FILTER functions to find the maximum value at the first date (FirstValue) and the maximum value at the last date (LastValue) within the selected period. Finally, we return the difference between the LastValue and FirstValue to get the change between the first and last numbers within the specified period.

You can use this "Change Between First and Last" measure in your reports or visuals to display the desired result.

[DebbieE]

Still stuggling. I dont have Date in my fact table. The Date is in the dim connected by DImDateKey so I had to amend slightly based on that.

It still comes to 7 though not 6.

Created value at start and Value at End as actual Measures and Start and Min Simply shows the Min and Max the Max

It feels like this should work so Im a bit lost

[123abc · Accepted solution]

I understand your requirement better now. You want to calculate the change between the first and last values within a specified period based on your Date dimension connected through a common key in your fact table. If the measures you've created are still resulting in 7 instead of 6, there might be an issue with the data or the calculation logic.

Let's try to create the measures again based on the Date dimension linked through a common key. You should have measures for "Value at Start" and "Value at End" as you mentioned, and then calculate the difference between these two values to get the desired change.

Let's try to create the measures again based on the Date dimension linked through a common key. You should have measures for "Value at Start" and "Value at End" as you mentioned, and then calculate the difference between these two values to get the desired change.

Value at Start:

Value at Start =
VAR MinDateKey = MIN('Dim Date'[DimDateKey])
RETURN
CALCULATE(
MAX('Fact Data'[Value]),
FILTER(
'Fact Data',
'Fact Data'[DimDateKey] = MinDateKey
)
)

Value at End:

Value at End =
VAR MaxDateKey = MAX('Dim Date'[DimDateKey])
RETURN
CALCULATE(
MAX('Fact Data'[Value]),
FILTER(
'Fact Data',
'Fact Data'[DimDateKey] = MaxDateKey
)
)

Change:

Make sure that your Date dimension and fact table are correctly linked through the DimDateKey. These measures should provide you with the correct change between the first and last values within the specified period. If you're still getting 7 instead of 6, please double-check your data and ensure that the dates and values are as expected in your tables.