Forum Discussion
Solved
Rotación de Cartera
¡Saludos Comunidad! Requiero por favor de su ayuda con el cálculo de la rotación de cartera por mes: Los datos utilizados son las Ventas a Crédito y el Saldo Final: Por ejemplo, mir...
hi aamira
Try this way as below:
Step1:
Add Year and Month column in Dimdate table
Step2:
Create three measure as below:
valor = CALCULATE(SUM(Datos[Saldo Final]),FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&DimDate[Month]=12))-CALCULATE(SUM(Datos[Ventas Credito]),FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&DimDate[Date]>=MAX(DimDate[Date])))MonthlyPortfolio Rotation = var _maxmonth=CALCULATE(MAX(DimDate[Month]),FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&[valor]<0)) return IF(MAX(DimDate[Month])=_maxmonth,CALCULATE([valor],FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&DimDate[Month]=_maxmonth+1))/DIVIDE(CALCULATE(SUM(Datos[Ventas Credito])),30),IF(MAX(DimDate[Month])>_maxmonth,30))Result = var _table=SUMMARIZE(DimDate,DimDate[Year],DimDate[Month],"_value",[MonthlyPortfolio Rotation]) return SUMX(_table,[_value])Result:
and here is sample pbix file, please try it.
Regards,
Lin
v-lili6-msft
Community Support
Community Supporthi aamira
Could you please share your sample pbix file for us have a test? that will be a great help.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Lin
- v-lili6-msft
hi aamira
Try this way as below:
Step1:
Add Year and Month column in Dimdate table
Step2:
Create three measure as below:
valor = CALCULATE(SUM(Datos[Saldo Final]),FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&DimDate[Month]=12))-CALCULATE(SUM(Datos[Ventas Credito]),FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&DimDate[Date]>=MAX(DimDate[Date])))MonthlyPortfolio Rotation = var _maxmonth=CALCULATE(MAX(DimDate[Month]),FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&[valor]<0)) return IF(MAX(DimDate[Month])=_maxmonth,CALCULATE([valor],FILTER(ALL(DimDate),DimDate[Year]=MAX(DimDate[Year])&&DimDate[Month]=_maxmonth+1))/DIVIDE(CALCULATE(SUM(Datos[Ventas Credito])),30),IF(MAX(DimDate[Month])>_maxmonth,30))Result = var _table=SUMMARIZE(DimDate,DimDate[Year],DimDate[Month],"_value",[MonthlyPortfolio Rotation]) return SUMX(_table,[_value])Result:
and here is sample pbix file, please try it.
Regards,
Lin