topic Re: Measures to take into account row context and dates for calculation in Desktop
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841207#M712345
<P>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.</P>Thu, 13 May 2021 17:08:06 GMTdaxer2021-05-13T17:08:06ZMeasures to take into account row context and dates for calculation
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841147#M712332
<P>Hi, it's a long problem but I hope to explain it well. Let's begin with the raw data:</P><P> </P><P>(Apologies for not being able to format the table properly here.)</P><TABLE><TBODY><TR><TD>Date</TD><TD>Group</TD><TD>Parent Group</TD><TD>IrrelevantColumn</TD><TD>Return</TD><TD>Return Index</TD><TD>Cumulative Returns Index</TD><TD>Prev_Month_Cumulative_Index</TD></TR><TR><TD>31-Dec-15</TD><TD>A</TD><TD>PG1</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>31-Dec-15</TD><TD>B</TD><TD>PG1</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>31-Dec-15</TD><TD>C</TD><TD>PG1</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>31-Dec-15</TD><TD>D</TD><TD>PG1</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>31-Dec-15</TD><TD>E</TD><TD>PG2</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>31-Dec-15</TD><TD>F</TD><TD>PG2</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>31-Dec-15</TD><TD>G</TD><TD>PG2</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>31-Dec-15</TD><TD>H</TD><TD>PG2</TD><TD>abc</TD><TD>0.00%</TD><TD>1.000</TD><TD>1.000</TD><TD>nan</TD></TR><TR><TD>29-Jan-16</TD><TD>A</TD><TD>PG1</TD><TD>abc</TD><TD>4.61%</TD><TD>1.046</TD><TD>1.046</TD><TD>1.000</TD></TR><TR><TD>29-Jan-16</TD><TD>B</TD><TD>PG1</TD><TD>abc</TD><TD>-3.57%</TD><TD>0.964</TD><TD>0.964</TD><TD>1.000</TD></TR><TR><TD>29-Jan-16</TD><TD>C</TD><TD>PG1</TD><TD>abc</TD><TD>-1.79%</TD><TD>0.982</TD><TD>0.982</TD><TD>1.000</TD></TR><TR><TD>29-Jan-16</TD><TD>D</TD><TD>PG1</TD><TD>abc</TD><TD>-5.97%</TD><TD>0.940</TD><TD>0.940</TD><TD>1.000</TD></TR><TR><TD>29-Jan-16</TD><TD>E</TD><TD>PG2</TD><TD>abc</TD><TD>0.06%</TD><TD>1.001</TD><TD>1.001</TD><TD>1.000</TD></TR><TR><TD>29-Jan-16</TD><TD>F</TD><TD>PG2</TD><TD>abc</TD><TD>4.51%</TD><TD>1.045</TD><TD>1.045</TD><TD>1.000</TD></TR><TR><TD>29-Jan-16</TD><TD>G</TD><TD>PG2</TD><TD>abc</TD><TD>-4.54%</TD><TD>0.955</TD><TD>0.955</TD><TD>1.000</TD></TR><TR><TD>29-Jan-16</TD><TD>H</TD><TD>PG2</TD><TD>abc</TD><TD>-3.79%</TD><TD>0.962</TD><TD>0.962</TD><TD>1.000</TD></TR><TR><TD>29-Feb-16</TD><TD>A</TD><TD>PG1</TD><TD>abc</TD><TD>2.12%</TD><TD>1.021</TD><TD>1.068</TD><TD>1.046</TD></TR><TR><TD>29-Feb-16</TD><TD>B</TD><TD>PG1</TD><TD>abc</TD><TD>-5.51%</TD><TD>0.945</TD><TD>0.911</TD><TD>0.877</TD></TR><TR><TD>29-Feb-16</TD><TD>C</TD><TD>PG1</TD><TD>abc</TD><TD>-3.34%</TD><TD>0.967</TD><TD>0.949</TD><TD>1.009</TD></TR><TR><TD>29-Feb-16</TD><TD>D</TD><TD>PG1</TD><TD>abc</TD><TD>4.30%</TD><TD>1.043</TD><TD>0.981</TD><TD>0.991</TD></TR><TR><TD>29-Feb-16</TD><TD>E</TD><TD>PG2</TD><TD>abc</TD><TD>-7.68%</TD><TD>0.923</TD><TD>0.924</TD><TD>0.931</TD></TR><TR><TD>29-Feb-16</TD><TD>F</TD><TD>PG2</TD><TD>abc</TD><TD>-4.95%</TD><TD>0.950</TD><TD>0.993</TD><TD>0.932</TD></TR><TR><TD>29-Feb-16</TD><TD>G</TD><TD>PG2</TD><TD>abc</TD><TD>-2.13%</TD><TD>0.979</TD><TD>0.934</TD><TD>0.974</TD></TR><TR><TD>29-Feb-16</TD><TD>H</TD><TD>PG2</TD><TD>abc</TD><TD>0.37%</TD><TD>1.004</TD><TD>0.966</TD><TD>0.930</TD></TR><TR><TD>31-Mar-16</TD><TD>A</TD><TD>PG1</TD><TD>abc</TD><TD>-2.48%</TD><TD>0.975</TD><TD>1.042</TD><TD>0.895</TD></TR><TR><TD>31-Mar-16</TD><TD>B</TD><TD>PG1</TD><TD>abc</TD><TD>-2.94%</TD><TD>0.971</TD><TD>0.884</TD><TD>0.847</TD></TR><TR><TD>31-Mar-16</TD><TD>C</TD><TD>PG1</TD><TD>abc</TD><TD>-1.03%</TD><TD>0.990</TD><TD>0.939</TD><TD>0.884</TD></TR><TR><TD>31-Mar-16</TD><TD>D</TD><TD>PG1</TD><TD>abc</TD><TD>0.31%</TD><TD>1.003</TD><TD>0.984</TD><TD>0.816</TD></TR><TR><TD>31-Mar-16</TD><TD>E</TD><TD>PG2</TD><TD>abc</TD><TD>3.84%</TD><TD>1.038</TD><TD>0.959</TD><TD>0.776</TD></TR><TR><TD>31-Mar-16</TD><TD>F</TD><TD>PG2</TD><TD>abc</TD><TD>4.93%</TD><TD>1.049</TD><TD>1.042</TD><TD>0.759</TD></TR><TR><TD>31-Mar-16</TD><TD>G</TD><TD>PG2</TD><TD>abc</TD><TD>6.88%</TD><TD>1.069</TD><TD>0.998</TD><TD>0.762</TD></TR><TR><TD>31-Mar-16</TD><TD>H</TD><TD>PG2</TD><TD>abc</TD><TD>1.69%</TD><TD>1.017</TD><TD>0.982</TD><TD>0.743</TD></TR></TBODY></TABLE><P> </P><P><U><STRONG>Raw Data</STRONG></U></P><P>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.</P><P><U><STRONG>Problem Statement</STRONG></U></P><P>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:</P><OL><LI>Find the smallest date in the date range (this smallest date will be common across all the groups)<UL><LI>FIRSDATE() / MINX() ?</LI><LI>Filter by groups (provided as a row context)</LI><LI>Find the cumulative returns to date, rebased on the cumulative returns of the earliest date.<UL><LI>E.g. for Group A for a period of Jan - Mar, their cumulative returns would be as they are, since it's currently based on Jan (more specifically, the start of Jan/end of Dec):<UL><LI>(Implicit) 1-Jan: 1.000</LI><LI>29-Jan: 1.046 </LI><LI>29-Feb: 1.068 </LI><LI>31-Mar: 1.042 </LI></UL></LI><LI>For a period of Feb - Mar, the same Group A will return:<UL><LI>(Implicit) 1-Feb: 1.000 <- 1.046/1.046</LI><LI>29-Feb: 1.021 <- 1.068/1.046</LI><LI>31-Mar: 0.996 <- 1.042/1.046</LI></UL></LI></UL></LI></UL></LI></OL><P><U><STRONG>Expected Output</STRONG></U></P><P>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:</P><P> </P><P>If date range of January - March is selected for groups A&B:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Wendeley-North_0-1620922393305.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/513879iBC0169421CE9F6A3/image-size/medium?v=v2&px=400" role="button" title="Wendeley-North_0-1620922393305.png" alt="Wendeley-North_0-1620922393305.png" /></span></P><P>If date range of February - March is selected for groups A, B & C:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Wendeley-North_0-1620923456485.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/513896i00474FDEE6C3D4A7/image-size/medium?v=v2&px=400" role="button" title="Wendeley-North_0-1620923456485.png" alt="Wendeley-North_0-1620923456485.png" /></span></P><P> </P><P><U><STRONG>Things I've tried:</STRONG></U></P><P>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. </P><P> </P><P>The following is the code anyway, if it helps:</P><LI-CODE lang="markup">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)</LI-CODE><DIV><DIV> <DIV><U><U><STRONG>Final Use Case</STRONG></U></U><DIV>I do hope to eventually use this in a line chart, with cumulative returns as the Y-axis and the date as an X-axis. The date ranges will likely to toggled via a slicer.<DIV> <DIV>Please let me know if there is anything else I can provide. </DIV></DIV></DIV></DIV></DIV></DIV>Thu, 13 May 2021 16:31:28 GMThttps://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841147#M712332Wendeley-North2021-05-13T16:31:28ZRe: Measures to take into account row context and dates for calculation
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841207#M712345
<P>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.</P>Thu, 13 May 2021 17:08:06 GMThttps://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841207#M712345daxer2021-05-13T17:08:06ZRe: Measures to take into account row context and dates for calculation
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841344#M712380
<P>Since I can't for some reason paste formatted code in here... well, you'll have to do with a picture. Sorry.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="daxer_0-1620931949933.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/513965i6E6FBFF8915CAA48/image-size/large?v=v2&px=999" role="button" title="daxer_0-1620931949933.png" alt="daxer_0-1620931949933.png" /></span></P><P> </P>Thu, 13 May 2021 18:52:59 GMThttps://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841344#M712380daxer2021-05-13T18:52:59ZRe: Measures to take into account row context and dates for calculation
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841708#M712473
<P>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?</P><P>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?</P><P>To answer your very initial question, yes the hierarchy is a natural one. Does that mean the variable onlyOneParentGroupVisible is no longer required? </P>Fri, 14 May 2021 01:39:01 GMThttps://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841708#M712473Wendeley-North2021-05-14T01:39:01ZRe: Measures to take into account row context and dates for calculation
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841907#M712521
<P>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 <LI-USER uid="239341"></LI-USER> !<BR /><BR />The final formula I used was:</P><LI-CODE lang="markup">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</LI-CODE><P> </P><P>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.</P><P> </P><P>Cheers.</P>Fri, 14 May 2021 03:44:36 GMThttps://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1841907#M712521Wendeley-North2021-05-14T03:44:36ZRe: Measures to take into account row context and dates for calculation
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1842490#M712668
<P><LI-USER uid="301880"></LI-USER> </P><P> </P><P>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.</P><P> </P><P>It's your choice not to follow my advice. On <A href="http://www.sqlbi.com" target="_blank" rel="noopener">www.sqlbi.com</A> 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.</P><P> </P><P>And yes, if the hierarchy is natural, then you can drop the condition on Parent Group being in scope.</P>Fri, 14 May 2021 09:26:50 GMThttps://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1842490#M712668daxer2021-05-14T09:26:50ZRe: Measures to take into account row context and dates for calculation
https://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1844676#M713264
<P>I'll bear that in mind and look into that further, including reading some articles on sqlbi.com. Many thanks once again. Cheers. </P>Mon, 17 May 2021 00:51:37 GMThttps://community.powerbi.com/t5/Desktop/Measures-to-take-into-account-row-context-and-dates-for/m-p/1844676#M713264Wendeley-North2021-05-17T00:51:37Z