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.
Hello everybody,
I would like to implement the following:
I have a DateTable in Power BI, a table "FSE" with data, and then a filtered Table table. This contains the dates of the last date of the year (relative to "FSE") and the corresponding value. I would like to visualize a lower triangular matrix that looks like this: (The color formatting is unimportant. The calculations/values are important)
In Excel I did this manually, the files are attached (pbix and excel file): https://we.tl/t-x5Zi1P1Zw1
For Power BI I have not been successful so far. Can someone help me?
Best regards, hwoehler
Information about calculations:
The calculation works according to the formula: new value = ((final value / initial value) ^(1 / n)) - 1
The divisor (initial value) is fixed per column (the year on the x-axis). It is a constant (per column!). The dividend (= the final value) increases by 1 until it reaches the year 2018 (maximum of year). The final value is therefore flexible. The exponent also changes the value n (= the duration in years). This increases logically over the years. Columnwise, one line is always started later, as before.
How the calculations work, you can see on the pictures here: https://we.tl/t-hEQNrFJ9FS
Solved! Go to Solution.
@hwoehler solution is attached, the core of it is following DAX measure
Value =
VAR __yearonColumn = CALCULATE( MAX ( FSE[Year Column] ), ALLEXCEPT( FSE, FSE[Year] ) )
VAR __cYear =MAX ( DateTable[Year] )
VAR __pYear = __cYear - 1
VAR __prevYear =
CALCULATE(
[Parv Last Value of Year],
ALL ( DateTable[Year]), FSE[Year] = __pYear )
VAR __currYear =
CALCULATE(
[Parv Last Value of Year],
ALLEXCEPT( FSE, FSE[Year] )
)
VAR __identity = __yearonColumn - __pYear
RETURN
IF ( __identity > 0 && __prevYear <> BLANK(),
(
DIVIDE( __currYear, __prevYear ) ^
DIVIDE( 1, __identity )
) - 1
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@hwoehler what is the conditional formatting based on?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@hwoehler this is how it looks like in Power BI, just need to conditional format it
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@hwoehler solution is attached, the core of it is following DAX measure
Value =
VAR __yearonColumn = CALCULATE( MAX ( FSE[Year Column] ), ALLEXCEPT( FSE, FSE[Year] ) )
VAR __cYear =MAX ( DateTable[Year] )
VAR __pYear = __cYear - 1
VAR __prevYear =
CALCULATE(
[Parv Last Value of Year],
ALL ( DateTable[Year]), FSE[Year] = __pYear )
VAR __currYear =
CALCULATE(
[Parv Last Value of Year],
ALLEXCEPT( FSE, FSE[Year] )
)
VAR __identity = __yearonColumn - __pYear
RETURN
IF ( __identity > 0 && __prevYear <> BLANK(),
(
DIVIDE( __currYear, __prevYear ) ^
DIVIDE( 1, __identity )
) - 1
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@hwoehler I think this is what you are looking for, it is from power bi
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |