Good afternoon, everybody!
This post comes from the previous one: https://community.powerbi.com/t5/Translated-Spanish-Desktop/Problema-con-el-modelo-de-datos-medidas-...
I have been able to solve the problems of the relationship between tables, managing to obtain the desired results:
1) Obtain a stock by instrument type based on a fact table (purchases and sales of instruments)
2) I have been able to relate the price list of these instruments (2nd fact table) to the movement table, via a table with a calendar dimension.
The drawback I currently have is at the calculation level in DAX: I am not being able to obtain the valuation for the previous period (i.e. stock to the previous period multiplied by the price of the previous period).
I'm based on what I could find on the following link: https://www.daxpatterns.com/cumulative-total-excel-2013/
Periods are monthly. The price list contains only one month-end closing price. Entries are daily but stock is monthly. I'm not needing to value a daily stock, since I don't have a daily price list.
Here are the steps I could create:
CotizacionActual:
=IF(HASONEVALUE(DimFCI[Number]);MAXX(CALCULATETABLE(TOPN(1; QuotesFCI; QuotesFCI[Date]); QuotesFCI;FILTER(ALL(DimCalendario);D imCalendario[PeriodoID]<=Max(DimCalendario[PeriodID]))); QuotesFCI[Quote]))
CotizacionAnterior:
=IF(HASONEVALUE(DimFCI[Number]);MAXX(CALCULATETABLE(CotizacionesFCI;FILTER(ALL(DimCalendario);D imCalendario[PeriodoID]<=Max(DimCalendario[PERIODID])-1)); QuotesFCI[Quote]))
TenenciaActual:
=CALCULATE(SUM(EntriesFCI[Quantity]);FILTER(ALL(DimCalendario);D imCalendario[PeriodoID]<=Max(DimCalendario[PeriodoID])))
TenenciaAnterior:
=CALCULATE(SUM(EntriesFCI[Quantity]);FILTER(ALL(DimCalendario);D imCalendario[PeriodoID]<=Max(DimCalendario[PeriodoID])-1))
Current Valuation:
=SUMX(VALUES(DimFCI[Number]); [CotizacionActual][TenenciaActual])
Previous Valuation:
=SUMX(VALUES(DimFCI[Number]); [CotizacionAnterior][TenenciaAnterior])
The Current Valuation measure works perfectly. But in ValuationAnterior, for some instruments it takes prices that do not correspond to the corresponding period. I've tried the following measure, but it doesn't bring possible values for the Minimum Valuation measure:
CotizaciónAnterior2:
=IF(HASONEVALUE(DimFCI[Number]);MAXX(CALCULATETABLE(TOPN(1; QuotesFCI; QuotesFCI[Date]); QuotesFCI;FILTER(ALL(DimCalendario);D imCalendario[PeriodoID]<=Max(DimCalendario[PeriodID]) -1)); QuotesFCI[Quote]))
I await your comments. If you need more information, I'm all ears.
PS: I'm working on excel, with Power Pivot.
