- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
DAX to find the change between two numbers at the start and end of a period
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- 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.
- 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
08-05-2024 02:20 AM | |||
09-01-2023 03:28 AM | |||
04-08-2024 01:01 AM | |||
11-21-2023 08:02 AM | |||
11-13-2023 05:08 PM |
User | Count |
---|---|
123 | |
79 | |
59 | |
58 | |
44 |
User | Count |
---|---|
179 | |
120 | |
82 | |
70 | |
53 |