Saldo de cierre para incluir el último saldo de apertura más la suma de las actividades posteriores hasta alguna fecha

[Syndicate_Admin]

Hola gurús de PBI!

Gracias de antemano por analizar este problema. Mi equipo y yo hemos contratado a un consultor de PBI, pero esto también lo dejó perplejo. XLS y PBIX aquí: https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

A continuación se muestra un conjunto de datos de inversión simulados en una sola tabla. Para cada Inversión, podemos comprar acciones (Compra) o vender acciones (Venta). De vez en cuando se revaloriza el valor de toda la Inversión (Revalorización del Mercado); sin embargo, no todas las inversiones se revalorizan. El desafío para mí es que los montos de compra y venta son montos marginales / transaccionales, mientras que la revaluación del mercado es un equilibrio en un momento dado.

Gol: El lector puede seleccionar una fecha de finalización, y la imagen (gráfico de barras o tarjeta) muestra el valor total de esa inversión en esa fecha.

Ejemplo 1: ¿Cuánto vale Happy Investment al 3 de enero de 2022?

La solución debe agregar la última revalorización del mercado al 31 de diciembre de 2021 (saldo de apertura) a cualquier compra y venta posterior a eso, pero antes de la fecha especificada del 3 de enero. En este caso, el 1 de enero de 2022 compramos $ 40,041 en Happy Investment.

921.198 + 40.041 = 961.239

Ejemplo 2: ¿Cuánto vale Sad Investment al 27 de junio de 2022?

Dado que no hay revalorización del mercado antes del 27 de junio de 2022, el valor total debe agregar todas las compras y ventas hasta esa fecha (es decir, el saldo de apertura es 0).

1.700.000 - 61.877 + 49.570 = 1.687.693

Lo que he probado después de pasar tiempo en estos foros, google y youtube:

1. usando Calculate(Sum(Filter w/ Anteriormente para obtener totales en ejecución de Compras o Ventas por Inversiones, pero no puedo hacer lo mismo para la Revalorización de Mercado
2. Usando VAR MAXX(Filter( para establecer una Fecha Máxima para obtener la última Revalorización de Mercado, pero no sé cómo agregar las Compras y Ventas después de esa fecha. ¡Estoy absolutamente perplejo aquí!

[Syndicate_Admin]

Configure una tabla de fechas, pero no la vincule a la tabla de datos. A continuación, pruebe

Investment Value = 
var maxDate = MAX('Date'[Date])
var currentInvestment = SELECTEDVALUE( 'Sheet1'[Investment] )
var lastRevaluation = CALCULATETABLE( TOPN(1, 'Sheet1', 'Sheet1'[Activity Date]),
    REMOVEFILTERS('Sheet1'),
    'Sheet1'[Investment] = currentInvestment,
    'Sheet1'[Activity Type] = "Market Revaluation",
    'Sheet1'[Activity Date] <= maxDate
)
var openingBalance = SELECTCOLUMNS( lastRevaluation, "@val", [Market Revaluation])
var openingBalanceDate = SELECTCOLUMNS( lastRevaluation, "@val", [Activity Date])
var totalPurchases = CALCULATE( SUM(Sheet1[Purchase Amount]), 
    REMOVEFILTERS( 'Sheet1'),
    'Sheet1'[Activity Type] = "Purchase",
    'Sheet1'[Investment] = currentInvestment,
    'Sheet1'[Activity Date] > openingBalanceDate && 'Sheet1'[Activity Date] <= maxDate
)
var totalSales = CALCULATE( SUM(Sheet1[Sale Amount]), 
    REMOVEFILTERS( 'Sheet1'),
    'Sheet1'[Activity Type] = "Sale",
    'Sheet1'[Investment] = currentInvestment,
    'Sheet1'[Activity Date] > openingBalanceDate && 'Sheet1'[Activity Date] <= maxDate
)
var closingBalance = openingBalance + totalPurchases + totalSales
return closingBalance

[Syndicate_Admin]

Gracias Johnt75, me tomó un tiempo diseccionar lo que estás haciendo (soy un principiante) pero ahora veo la lógica.

Recibo un error cuando coloco [Valor de inversión] en cualquier visual.

Creo que es porque el campo Inversión es un campo de texto?

He guardado mi intento en esta carpeta (respuesta 2022.06.22 a johnt): https://drive.google.com/drive/folders/11WZKuWiNRz7vYAKmY0SP0gRwj8-qWJjo?usp=sharing

También he agregado una tabla de fechas independiente como usted sugirió.

[Syndicate_Admin]

Tiene un error tipográfico en la línea 22, está comparando 'Datos' [Fecha de actividad] en lugar de 'Datos' [Tipo de actividad].

Debería ser

Investment Value = 

VAR
    maxDate = max('date 1'[Date 1])
VAR
    currentInvestment = SELECTEDVALUE('Data'[Investment])
VAR
    lastRevaluation = calculatetable ( TOPN( 1, 'Data', 'Data'[Activity Date] ),
    REMOVEFILTERS(Data),
        Data[Investment] = currentInvestment,
        Data[Activity Type] = "Market Revaluation",
        Data[Activity Date] <= maxDate
    )

VAR 
    openingBalance = SELECTCOLUMNS(lastRevaluation, "@val", [Market Revaluation])
VAR
    openingBalanceDate = SELECTCOLUMNS( lastRevaluation, "@val", [Activity Date])
VAR
    totalPurchases = CALCULATE( sum(Data[Purchase Amount]),
    REMOVEFILTERS('data'),
        Data[Activity Type] = "Purchase",
        Data[Investment] = currentInvestment,
        Data[Activity Date] > openingBalanceDate && Data[Activity Date] <= maxDate
    )

VAR
    totalSales = CALCULATE( sum(Data[Sale Amount]),
    REMOVEFILTERS('Data'),
        Data[Activity Type] = "Sale",
        Data[Investment] = currentInvestment,
        Data[Activity Date] > openingBalanceDate && Data[Activity Date] <= maxDate
    )

VAR
    closingBalance = openingBalance + totalPurchases + totalSales

RETURN

closingBalance
    

[Syndicate_Admin]

No puedo creer que esto realmente esté funcionando. 😲

¡Gracias! Esto ayudó más de lo que sabes.