Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to display a graph that shows the performance of a portfolio. Currently it looks like this :
As you can see, the first value displayed is 101.63, because this is the value of the portfolio at the begining
of the selected date range.
I would like to dynamically set the first value to a 100 and adjust all values accordingly. For example, if the values
in the date range are as follows :
day 1 : 110
day 2 : 115
day 3 : 105
I would like then to be :
day 1: 100
day2 : 104.54
day3 : 95.45
The calculation is done by dividing each of the values by the value of the first day(110), and maltiplying by 100.
I don't know how to do it in DAX.
You help is appreciated, thanks !
Solved! Go to Solution.
Hi @zivhimmel
I would do something like this:
(I'm calling your fact table Data and assuming you have a related calendar table called Calendar with the relationship on the Date column, and a base measure called [Value] )
Value First Date = /* Optional check: Only return Value First Date up to the max date that actually appears in the fact table */ IF ( MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ), CALCULATE ( [Value], CALCULATETABLE ( FIRSTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) ) )
Value Normalized = DIVIDE ( [Value], [Value First Date] ) * 100
Hi @zivhimmel
I would do something like this:
(I'm calling your fact table Data and assuming you have a related calendar table called Calendar with the relationship on the Date column, and a base measure called [Value] )
Value First Date = /* Optional check: Only return Value First Date up to the max date that actually appears in the fact table */ IF ( MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ), CALCULATE ( [Value], CALCULATETABLE ( FIRSTDATE ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) ) )
Value Normalized = DIVIDE ( [Value], [Value First Date] ) * 100
Excellent!
I would nee to set the value to 100 to ANY first date taken into account for analysis and then normalize subsequent values accordingly.
Is this possible in DAX?
Very Nice Example!
I would need as similar method that calculatyes 100 as ANY first date taken in to account as starting date, and then normalize values accordingly. Is it possible with DAX ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |