cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wendeley-North
Resolver I
Resolver I

Measures to take into account row context and dates for calculation

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.)

DateGroupParent GroupIrrelevantColumnReturnReturn IndexCumulative Returns IndexPrev_Month_Cumulative_Index
31-Dec-15APG1abc0.00%1.0001.000nan
31-Dec-15BPG1abc0.00%1.0001.000nan
31-Dec-15CPG1abc0.00%1.0001.000nan
31-Dec-15DPG1abc0.00%1.0001.000nan
31-Dec-15EPG2abc0.00%1.0001.000nan
31-Dec-15FPG2abc0.00%1.0001.000nan
31-Dec-15GPG2abc0.00%1.0001.000nan
31-Dec-15HPG2abc0.00%1.0001.000nan
29-Jan-16APG1abc4.61%1.0461.0461.000
29-Jan-16BPG1abc-3.57%0.9640.9641.000
29-Jan-16CPG1abc-1.79%0.9820.9821.000
29-Jan-16DPG1abc-5.97%0.9400.9401.000
29-Jan-16EPG2abc0.06%1.0011.0011.000
29-Jan-16FPG2abc4.51%1.0451.0451.000
29-Jan-16GPG2abc-4.54%0.9550.9551.000
29-Jan-16HPG2abc-3.79%0.9620.9621.000
29-Feb-16APG1abc2.12%1.0211.0681.046
29-Feb-16BPG1abc-5.51%0.9450.9110.877
29-Feb-16CPG1abc-3.34%0.9670.9491.009
29-Feb-16DPG1abc4.30%1.0430.9810.991
29-Feb-16EPG2abc-7.68%0.9230.9240.931
29-Feb-16FPG2abc-4.95%0.9500.9930.932
29-Feb-16GPG2abc-2.13%0.9790.9340.974
29-Feb-16HPG2abc0.37%1.0040.9660.930
31-Mar-16APG1abc-2.48%0.9751.0420.895
31-Mar-16BPG1abc-2.94%0.9710.8840.847
31-Mar-16CPG1abc-1.03%0.9900.9390.884
31-Mar-16DPG1abc0.31%1.0030.9840.816
31-Mar-16EPG2abc3.84%1.0380.9590.776
31-Mar-16FPG2abc4.93%1.0491.0420.759
31-Mar-16GPG2abc6.88%1.0690.9980.762
31-Mar-16HPG2abc1.69%1.0170.9820.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:

  1. Find the smallest date in the date range (this smallest date will be common across all the groups)
    • FIRSDATE() / MINX() ?
    • Filter by groups (provided as a row context)
    • Find the cumulative returns to date, rebased on the cumulative returns of the earliest date.
      • 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):
        • (Implicit) 1-Jan: 1.000
        • 29-Jan: 1.046 
        • 29-Feb: 1.068 
        • 31-Mar: 1.042 
      • For a period of Feb - Mar, the same Group A will return:
        • (Implicit) 1-Feb: 1.000 <- 1.046/1.046
        • 29-Feb: 1.021 <- 1.068/1.046
        • 31-Mar: 0.996 <- 1.042/1.046

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:

Wendeley-North_0-1620922393305.png

If date range of February - March is selected for groups A, B & C:

Wendeley-North_0-1620923456485.png

 

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)
 
Final Use Case
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.
 
Please let me know if there is anything else I can provide. 
1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

Since I can't for some reason paste formatted code in here... well, you'll have to do with a picture. Sorry.

 

daxer_0-1620931949933.png

 

View solution in original post

6 REPLIES 6
daxer
Solution Sage
Solution Sage

Since I can't for some reason paste formatted code in here... well, you'll have to do with a picture. Sorry.

 

daxer_0-1620931949933.png

 

View solution in original post

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 @daxer !

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.

@Wendeley-North 

 

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. 

daxer
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - July 22, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors