Owen’s Sample Power BI Reports

Here are some links to Power BI reports I have created:

1. Sales by Customer with dynamic Top N & Bottom N

Sales Amount Top =
 CALCULATE (
     [Sales Amount],
     TOPN (
         [TopN Selection],
         CALCULATETABLE ( VALUES ( Sales[Customer] )ALL ( Sales[Customer] ) ),
         [Sales Amount]
     ),
     VALUES ( Sales[Customer] )
 )
Sales Amount Bottom =
 CALCULATE (
     [Sales Amount],
     TOPN (
         [BottomN Selection],
         CALCULATETABLE ( VALUES ( Sales[Customer] )ALL ( Sales[Customer] ) ),
         [Sales Amount], ASC
     ),
     VALUES ( Sales[Customer] )
 )
Sales Amount Other =
VAR NonBlankCustomers =
    CALCULATETABLE ( VALUES ( Sales[Customer] )ALL ( Sales[Customer] ) )
RETURN
    CALCULATE (
        [Sales Amount],
        EXCEPT (
            ALL ( Sales[Customer] ),
            UNION (
                TOPN ( [TopN Selection], NonBlankCustomers, [Sales Amount] ),
                TOPN ( [BottomN Selection], NonBlankCustomers, [Sales Amount], ASC )
            )
        ),
        VALUES ( Sales[Customer] )
    )

Note: The reason for using

CALCULATETABLE ( VALUES ( Sales[Customer] )ALL ( Sales[Customer] ) )

rather than

ALL ( Sales[Customer] )

within TOPN is to ensure that customers with blank sales are excluded from the ranking. This is important for the bottom-ranked customers, as customers with blank sales would otherwise be ranked below customers with positive sales.

2. Time-Weighted Return and Money-Weighted Return

Model setup

Suppose we have a number of investment portfolios managed  by separate managers, that each start with $100 on 1/1/2015, earn a random return each day, and receive external cash flows on certain days (for the sake of an example, an inflow of $10 at the end of every Monday).

An extract of the source data looks like this:

Return

‘Value Open’ and ‘Value Close’ are the portfolio opening/closing values on a given day, and ‘Cashflow’ is the external cash flow occurring at the end of that day.

This table is loaded to Power BI Desktop as ‘Value’, with the Return column eliminated. Assume that in the real world, the portfolios are valued daily but Return is not explicitly calculated.

Power BI Return

This table is then related to a ‘Date’ dimension table which is extended so that it includes at least one date before the first ‘Value’ date (in fact I included an entire extra year).

Power BI Return diagram

Return Measures

For reference, Time-Weighted and Money-Weighted Rates of Return definitions are here.

To calculate the Time-Weighted Rate of Return we need to geometrically link returns (from the first date to the last date) between external cash flows, ensuring that the external cash flows are not included included in any return values. In practice, we can just include every date in the calculation regardless of whether cash flow is positive, zero or blank.

Before creating the return measures, I created some intermediate measures:

Value Open Total =
SUM ( 'Value'[Value Open] )

Value Close Total =
SUM ( 'Value'[Value Close] )

Cashflow Total =
SUM ( 'Value'[Cashflow] )

Then the Time-Weighted Return measure is:

Time-Weighted Return (non ann) =
PRODUCTX (
    'Date',
    DIVIDE ( [Value Close Total] - [Cashflow Total], [Value Open Total] )
)
    1

This is the return in the current period rather than an annualised return. It can be annualised as:

Time-Weighted Return (ann) =
POWER (
    1 + [Time-Weighted Return (non ann)],
    DIVIDE ( 365COUNTROWS ( 'Date' ) )
)
    1

For Money-Weighted Rate of Return, since it is a form of Internal Rate of Return, we can use the DAX XIRR function. The measure definition is more complicated than Time-Weighted Rate of Return because the first and last dates are special cases, and, to be pedantic, the first date (which is treated as time zero by XIRR) should be one day before the first date selected.

The ‘cash flows’ we want on each date are:

  • Day before first date selected: (-1)*<Opening value on first day>
  • First date selected to second-to-last date selected: (-1)*<External cash flow>
  • Last date selected: (-1)*<External cash flow> + <Closing value on last day>

The resulting measure is:

Money-Weighted Return (ann) =
VAR DayBeforeFirstDate =
    PREVIOUSDAY ( FIRSTDATE ( 'Date'[Date] ) )
VAR DateExtended =
    UNION ( DayBeforeFirstDate, VALUES ( 'Date'[Date] ) )
VAR InitialCF =
    CALCULATE ( [Value Open Total], FIRSTDATE ( 'Date'[Date] ) )
RETURN
    IFERROR (
        XIRR (
            DateExtended,
            IF (
                'Date'[Date] = DayBeforeFirstDate,
                ( -1 ) * InitialCF,
                - [Cashflow Total]
                    IF ( 'Date'[Date] = MAX ( 'Date'[Date] ), [Value Close Total] )
            ),
            'Date'[Date]
        ),
        BLANK ()
    )