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, it's a long problem but I hope to explain it well. Let's begin with the raw data:
(Apologies for not being able to format the table properly here.)
Date | Group | Parent Group | IrrelevantColumn | Return | Return Index | Cumulative Returns Index | Prev_Month_Cumulative_Index |
31-Dec-15 | A | PG1 | abc | 0.00% | 1.000 | 1.000 | nan |
31-Dec-15 | B | PG1 | abc | 0.00% | 1.000 | 1.000 | nan |
31-Dec-15 | C | PG1 | abc | 0.00% | 1.000 | 1.000 | nan |
31-Dec-15 | D | PG1 | abc | 0.00% | 1.000 | 1.000 | nan |
31-Dec-15 | E | PG2 | abc | 0.00% | 1.000 | 1.000 | nan |
31-Dec-15 | F | PG2 | abc | 0.00% | 1.000 | 1.000 | nan |
31-Dec-15 | G | PG2 | abc | 0.00% | 1.000 | 1.000 | nan |
31-Dec-15 | H | PG2 | abc | 0.00% | 1.000 | 1.000 | nan |
29-Jan-16 | A | PG1 | abc | 4.61% | 1.046 | 1.046 | 1.000 |
29-Jan-16 | B | PG1 | abc | -3.57% | 0.964 | 0.964 | 1.000 |
29-Jan-16 | C | PG1 | abc | -1.79% | 0.982 | 0.982 | 1.000 |
29-Jan-16 | D | PG1 | abc | -5.97% | 0.940 | 0.940 | 1.000 |
29-Jan-16 | E | PG2 | abc | 0.06% | 1.001 | 1.001 | 1.000 |
29-Jan-16 | F | PG2 | abc | 4.51% | 1.045 | 1.045 | 1.000 |
29-Jan-16 | G | PG2 | abc | -4.54% | 0.955 | 0.955 | 1.000 |
29-Jan-16 | H | PG2 | abc | -3.79% | 0.962 | 0.962 | 1.000 |
29-Feb-16 | A | PG1 | abc | 2.12% | 1.021 | 1.068 | 1.046 |
29-Feb-16 | B | PG1 | abc | -5.51% | 0.945 | 0.911 | 0.877 |
29-Feb-16 | C | PG1 | abc | -3.34% | 0.967 | 0.949 | 1.009 |
29-Feb-16 | D | PG1 | abc | 4.30% | 1.043 | 0.981 | 0.991 |
29-Feb-16 | E | PG2 | abc | -7.68% | 0.923 | 0.924 | 0.931 |
29-Feb-16 | F | PG2 | abc | -4.95% | 0.950 | 0.993 | 0.932 |
29-Feb-16 | G | PG2 | abc | -2.13% | 0.979 | 0.934 | 0.974 |
29-Feb-16 | H | PG2 | abc | 0.37% | 1.004 | 0.966 | 0.930 |
31-Mar-16 | A | PG1 | abc | -2.48% | 0.975 | 1.042 | 0.895 |
31-Mar-16 | B | PG1 | abc | -2.94% | 0.971 | 0.884 | 0.847 |
31-Mar-16 | C | PG1 | abc | -1.03% | 0.990 | 0.939 | 0.884 |
31-Mar-16 | D | PG1 | abc | 0.31% | 1.003 | 0.984 | 0.816 |
31-Mar-16 | E | PG2 | abc | 3.84% | 1.038 | 0.959 | 0.776 |
31-Mar-16 | F | PG2 | abc | 4.93% | 1.049 | 1.042 | 0.759 |
31-Mar-16 | G | PG2 | abc | 6.88% | 1.069 | 0.998 | 0.762 |
31-Mar-16 | H | PG2 | abc | 1.69% | 1.017 | 0.982 | 0.743 |
Raw Data
To begin with, the columns [Cumulative_Returns_Index] and [Prev_Month_Cumulative_Index] are not part of the raw data, but I created them as helper columns in Excel to make it easier to explain what the results should be. If possible, please let me know how to create them either as calculated columns, or have everything done as measures.
Problem Statement
I would like to create a measure that will take into account a date range (defined via a slicer), and then calculate the cumulative returns of the group, rebased on the earliest date in the date range. I've broken down the problem as much as I can to hopefully try to make it easier to get some help:
Expected Output
The expected output, together with the other groups present (using just A, B and C to prevent the list from getting too long), should look like this:
If date range of January - March is selected for groups A&B:
If date range of February - March is selected for groups A, B & C:
Things I've tried:
I've created the [Cumulative_Returns_Index] and [Prev_Month_Cumulative_Index] via Python before importing the data to PowerBI (but would like to process the raw data if possible), and have used many hours to come up with the following code that didn't work when more than 1 group was present in the visualisation (table). I suspect it being due to multiple ref_indexes being returned, but PowerBI somehow couldn't identify the correct groups to use it for via the row context provided in the visualization.
The following is the code anyway, if it helps:
Rebal_Cumu_Returns_AllSel =
VAR ref_index =
CALCULATE(
VALUES( table[Prev_Mth_Cumulative_Returns_Index]),
FILTER( ALLSELECTED(table),
table[Date] = MINX(ALLSELECTED(table), table[Date]))
)
RETURN
DIVIDE(SELECTEDVALUE(table[Cumulative_Returns_Index]), ref_index)
Solved! Go to Solution.
Since I can't for some reason paste formatted code in here... well, you'll have to do with a picture. Sorry.
Since I can't for some reason paste formatted code in here... well, you'll have to do with a picture. Sorry.
Hi, thanks for providing an answer. Just wanted to clarify - does Date[Year-Month] indicate that I have to create a table that summarises the list of dates I have in my fact table into the YYYY-MM format?
In addition, in line 40 above, is ALLSELECTED(Dates) supposed to be ALLSELECTED(DateTable), where DateTable refers to the aforementioned table created above in the YYYY-MM format?
To answer your very initial question, yes the hierarchy is a natural one. Does that mean the variable onlyOneParentGroupVisible is no longer required?
Hi just wanted to add - since the fact table by nature has a unique months across all years, it seemed like the date table could be dropped as well. I've amended the formula accordingly, and it seems like it works. Thanks @Anonymous !
The final formula I used was:
Cumulative_Returns_Index_Rebased =
VAR isGroupInScope = ISINSCOPE( table[Group] )
VAR shouldCalculate = True
&& isGroupInScope
VAR Result =
IF( shouldCalculate,
VAR firstCumulativeReturnsIndex =
MAXX(
CALCULATETABLE(
TOPN( 1,
table,
table[Date],
ASC
),
ALLSELECTED( table[Date] )
),
table[Cumulative_Returns_Index]
)
VAR currentCumulativeIndex =
SELECTEDVALUE( table[Cumulative_Returns_Index] )
VAR relativeCumulativeIndex =
DIVIDE( currentCumulativeIndex,
firstCumulativeReturnsIndex
)
RETURN
relativeCumulativeIndex
)
RETURN
Result
If there are any concerns regarding the changes I've made (to exclude the creation of a date table and the checking of the parent group) that you think could potentially lead to problems down the line, do let me know.
Cheers.
All I can tell you is this. If you have a model where you don't have separate tables for dimensions, especially the date/time dimension... then 100% you're doing it totally wrong. Don't be surprised when you one day you'll discover that your figures not always are correct. But since it's very difficult to immediately spot the problems you'll certainly have one day (because this depends - surprise, surprise! - on the distribution of data in columns), you'll be basing your decisions on wrong figures.
It's your choice not to follow my advice. On www.sqlbi.com there's is an article by Alberto Ferrari that explains why one-table model will in certain circumstances yield wrong figures and you can't do anything about it apart from... building a correct model, which means STAR-SCHEMA.
And yes, if the hierarchy is natural, then you can drop the condition on Parent Group being in scope.
I'll bear that in mind and look into that further, including reading some articles on sqlbi.com. Many thanks once again. Cheers.
First question. Is the hierarchy Parent Group -> Group a natural one? A natural hierarchy is one that has unique leaves. So, for instance, you can't have any leaf (think: Group) below 2 different parent groups. Is this true? Because if not, then you have to say how you want to calculate when the same group, say A, is visible in the current context but no Parent Group has been selected.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |