Crecimiento de compuestos usando DAX – R Marketing

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Hay algunas cosas que son muy fáciles de hacer en Excel que no parecen tener una solución inmediatamente obvia y simple en DAX. Uno de esos problemas es cuando desea aplicar un crecimiento compuesto a una inversión. Permítanme inventar un ejemplo para que quede claro de qué estoy hablando. Los ejemplos usados ​​en esta publicación se pueden aplicar al VPN usando la función XNPV.

Ejemplo simple: tasa de crecimiento fija

Supongamos que realiza una inversión de $ 10,000 en una acción y desea pronosticar el valor de esa inversión en 10 años teniendo en cuenta una tasa de crecimiento anual proyectada. Así es como puede hacer eso en Excel.

image_thumb-6288467

Comience con el valor de la inversión en el año 0. Luego, en todos los años futuros, multiplique el valor del año anterior por (1 + tasa de crecimiento anual), o 1.08 en el ejemplo anterior. Lo que hace que Excel sea una herramienta tan fácil de usar es que puede escribir una fórmula para el año 1 y luego copiarla en la página. Cada fórmula posterior toma el resultado del año anterior y agrega el crecimiento del año actual al cálculo anterior.

Para el ejemplo anterior, puede usar una fórmula simple de Excel de celda única para determinar el valor en un año futuro de la siguiente manera, sin tener que calcular todos los valores para cada año uno a la vez.

=FV(0.08,10,0,-10000)

La función anterior aplica una tasa de inversión del 8%, durante 10 años, haciendo 0 pagos adicionales, sobre una inversión inicial de $ 10,000. Esta fórmula simple devuelve el mismo valor de $ 21.589 en el año 10; igual que en el ejemplo de Excel anterior.

Ejemplo más difícil: tasa de crecimiento variable

Es más difícil escribir una fórmula / función de una sola celda cuando tienes una tabla de tasas de crecimiento que varían con el tiempo. Pero este ejemplo más difícil sigue siendo bastante simple en un libro de Excel normal, como se muestra a continuación.

image_thumb-1-8040090

La fórmula sigue siendo esencialmente la misma que la del primer ejemplo de Excel simple al comienzo de este artículo, pero esta vez el cálculo utiliza la tasa prevista para cada año en el cálculo año por año en lugar de una tasa anual fija.

Crecimiento compuesto usando DAX

Ahora volvamos al punto de este artículo. El crecimiento compuesto es muy fácil de hacer en Excel porque puede escribir fórmulas de celda individuales (para hacer lo que quiera), y cada nueva fórmula puede hacer referencia a la respuesta de la fórmula anterior como punto de partida para la nueva fórmula. No existe tal capacidad en el lenguaje DAX. Para resolver este tipo de problemas en DAX, debe cambiar su forma de pensar y comenzar a pensar en cómo escribir una fórmula única que funcione en una TABLA completa de datos (o columnas o tablas múltiples); no hay fórmulas individuales celda por celda. posible.

A continuación, lo guiaré a través del proceso para encontrar una solución a este problema. Como menciono a menudo en mis artículos, es el proceso que creo que es más importante. Rara vez sé cómo responder a un problema complejo de DAX cuando empiezo (esa es la definición misma de “complejo”) y, en cambio, sigo un proceso para ayudarme a resolver el problema. Lea detenidamente a continuación. Si aplica el mismo proceso cuando escribe sus fórmulas, estará bien encaminado para convertirse en un superhéroe de DAX.

Paso 1 – Cargue los datos

Como ya estoy en Excel, cargué los datos de muestra del ejemplo anterior en Power Pivot para Excel 2016. Los resultados serían los mismos si resolviera el problema con Power BI Desktop.

image_thumb-2-4547750

Tenga en cuenta que he cargado los números de año como Enteros en lugar del texto “Año 1, Año 2”, etc. Esta es una parte esencial de la solución a este problema, ya que tiene un índice ordinal que indica el orden de las filas en la tabla.

Paso 2: crear una tabla dinámica

Este es uno de mis consejos más importantes y le recomendamos que le preste atención. Antes de hacer cualquier otra cosa, cree una tabla dinámica para que pueda «ver» los datos que residen en la tabla subyacente. Esto es particularmente cierto para la gente de Excel (como yo), ya que estamos acostumbrados a «ver» cosas en las que estamos trabajando. Es mucho más difícil escribir fórmulas DAX complejas cuando no está viendo una muestra de sus datos en la pantalla. Si está escribiendo DAX en Power BI Desktop, le recomiendo que agregue una matriz al informe y la use como punto de partida.

Aquí está mi tabla dinámica, en formato tabular, con los subtotales desactivados y el Año y la Tasa colocados en Filas. He escrito una medida simple Inversión inicial = 10000 y lo colocó en Valores en la tabla dinámica.

image_thumb-3-3737821

Paso 3: pensar en el problema

Ahora que puede «ver» los datos que provienen de la tabla en la tabla dinámica de arriba, es más fácil pensar en el problema. Solo como un recordatorio sobre el «desafío» en cuestión; necesitamos poder escribir una fórmula única que dé el resultado correcto para cada fila de la tabla; no hay oportunidad de hacer cálculos celda por celda y no hay oportunidad de «señalar» el resultado anterior e incluirlo dentro de cualquier fórmula .

El segundo concepto que necesita para entenderlo es pensando en sus datos como «tablas de datos con filtros aplicados (o no)». Solo puede haber una fórmula que dé los resultados correctos, y cada resultado será diferente debido a la diferentes filtros que se aplicará a la tabla de datos subyacente. Con eso en mente, permítame mostrarle lo que necesitamos como fórmula para calcular el valor futuro en cualquier año.

Año 0 = 10,000 * (1+ 0.00)

Año 1 = 10,000 * (1+ 0.00) * (1 + 0.08)

Año 2 = 10,000 * (1+ 0.00) * (1 + 0.08) * (1 + 0.07)

Año 3 = 10,000 * (1+ 0.00) * (1 + 0.08) * (1 + 0.07) * (1 + 0.06)

Año 4 = 10,000 * (1+ 0.00) * (1 + 0.08) * (1 + 0.07) * (1 + 0.06) * (1 + 0.06)

… etcétera.

Por lo tanto, debe identificar el patrón anterior como “para cada año, multiplique la inversión inicial por (1 + tasa de crecimiento) para cada año hasta el año actual. En referencia a mi consejo anterior, debería quedar claro ahora que necesita filtrar la tabla de datos para poder acceder al año actual y todos los años antes de completar el cálculo. Eso está empezando a parecerme un patrón de FILTRO estándar.

super-charge-power-bi-ad_1-1024x128-6595595

Paso 4: escriba una medida de prueba provisional

Lo siguiente que debe hacer es escribir una medida de prueba intermedia que “pruebe” un patrón de filtro que mantenga cada fila de la tabla hasta la fila actual inclusive. El resultado final de la medida de prueba que escribí se muestra a continuación; devuelve un recuento de cuántas filas se filtran para cada fila de la tabla dinámica.

FilteredRows = 
    VAR LatestYear = MAX(Rates[Year])
    VAR UnfilteredTable = ALL(Rates)
    RETURN
       COUNTROWS(FILTER(UnfilteredTable, Rates[Year] <= LatestYear))

image_thumb-4-2727433

Las medidas de prueba como esta son excelentes para ayudarlo a resolver el problema al dividir el problema en pedazos y permitirle «ver» los resultados a medida que avanza. Puedo «ver» arriba de mi medida de prueba que la tabla filtrada está creciendo a medida que avanza en la tabla dinámica. También he usado la sintaxis VAR para escribir mi medida. Cada vez escribo más mis medidas de esta manera, ya que hace que las fórmulas se documenten casi por sí mismas y sean más fáciles de leer y comprender para los demás. Por autodocumentación, quiero decir que los nombres de las variables son descriptivos de lo que hace cada parte de la fórmula, y eso hace que la fórmula sea más fácil de entender.

Paso 5: escriba la medida final

La medida final debe multiplicar la inversión inicial por (1 + tasa de crecimiento) para cada fila de la tabla después de que se haya aplicado el filtro. ¡Eso suena como una función X iterativa! Sé que hay SUMX, MINX, MAXX, etc., así que fui a buscar (usando Intellisense) un MULTIPLYX y TIMESX, pero tuve suerte. Entonces recordé mi Guía de referencia rápida de DAX que preparé hace unos años; puede descargar una copia gratis aquí. Un vistazo rápido al PDF y encontré lo que estaba buscando: PRODUCTX. Esta función se lanzó en Excel 2016 y Power BI Desktop, por lo que no tiene suerte si usa Excel 2013 o una versión anterior.

Aquí está mi fórmula final (reutilizando las entrañas de la medida de prueba provisional de arriba).

Future Value =
    VAR LatestYear = MAX(Rates[Year])
    VAR UnfilteredTable = ALL(Rates)
    RETURN
      [Initial Investment] 
          * CALCULATE(
                PRODUCTX(Rates, 1+Rates[Expected Growth Rate]),
                FILTER(UnfilteredTable, Rates[Year] <= LatestYear)
            )

Y aquí están los resultados de esta medida en mi tabla dinámica (las mismas respuestas que la versión de Excel en la parte superior de la página wlemoticon-smile-7744870 ).

image_thumb-5-5558745

La forma en que funciona la fórmula anterior es

Primero, el filtro se aplica a la tabla, por lo que solo las filas que deben incluirse permanecen en el contexto del filtro. Luego, la función PRODUCTX itera sobre esta tabla filtrada, una fila a la vez, multiplicando (1 + tasa) por cada fila en la tabla filtrada. Finalmente, este factor compuesto se multiplica por la inversión inicial.

También puedes leer mi artículo sobre Crecimiento especificado por el usuario en una inversión con Power BI.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.