Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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.
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.
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:
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.
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.
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?
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.
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
107 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
94 | |
91 |