cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DiogoValentim
New Member

Help creating two mesures

Hello to all, 

 

I need a big help creating two mesures:

 

1st - Fees for the Shareholders : I need help creating a formula that Calculate the amont of fee one shareholder must Pay, to do so I must create a sum of fees of "Forecast" by date (Month) multiplied by the share that shareholder holds in the SPE (Company)

 

Translates:

  • the name "Acionista" = Shareholder
  • The names "Previsao" or "Previsão" = Forecast (Fees)
  • Dcalendario = My date modeling: Data Base=Range of Dates  |   Ano=Year  |   Mês=Month  |   Dia=Day  |   Trimestre=Quarter
  • The Name Participação=Share

2nd - Create a Mesure that see the amount of money one shareholder imput in the SPE (Company) by time less the Fees for the Shareholders create in the first question.

 

Translates:

  • the name "Aporte" = Money imput by the Shareholder on one month.


The logic behind this Mesures is first, identify the need of pay for any shareholder, and the need of pay equals to the sum of "Previsão" multiplied by the share of each shareholder.

After get what each shareholder must pay, we see the amount of money thei inject in this company, and see if the shareholder has a debt or a credit (Aporte), this seen by time (Year, quarter or month)

 

The PBI file and database is in the link: https://goo.gl/F0EY3E

 

Thx for the help, and if i get myself unclear, just ask anyting.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Help creating two mesures

Hi @DiogoValentim,

 

You can take a look at below formulas if it works on your side:

 

Previsao(Data&SPE) =
var currSPE=LASTNONBLANK(Tbl_PlanodeContas[SPE],[SPE])
var currDate=MAX([Data])
Return
SUMX(FILTER(ALL(Tbl_PlanodeContas),[SPE]=currSPE&&[Data]=currDate),[Valor])

 

Current Spend =
var currDate=MAX([Data])
var currSPE=LASTNONBLANK(Tbl_AporteAcionista[SPE],[SPE])
var currAcionista=LASTNONBLANK(Tbl_AporteAcionista[nAcionista],[nAcionista])
Return
SUMX(FILTER(ALL(Tbl_AporteAcionista), [nAcionista]=currAcionista&&[SPE]=currSPE&&[Data]=currDate),[Aporte])

 

Need Spend =
CALCULATE([Previsao(Data&SPE)],VALUES(Tbl_AcionistaEmpresa[SPE]))* MAX([Participacao])

 

Result:

 

12.pngCapture3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

2 REPLIES 2
Community Support
Community Support

Re: Help creating two mesures

Hi @DiogoValentim,

 

You can take a look at below formulas if it works on your side:

 

Previsao(Data&SPE) =
var currSPE=LASTNONBLANK(Tbl_PlanodeContas[SPE],[SPE])
var currDate=MAX([Data])
Return
SUMX(FILTER(ALL(Tbl_PlanodeContas),[SPE]=currSPE&&[Data]=currDate),[Valor])

 

Current Spend =
var currDate=MAX([Data])
var currSPE=LASTNONBLANK(Tbl_AporteAcionista[SPE],[SPE])
var currAcionista=LASTNONBLANK(Tbl_AporteAcionista[nAcionista],[nAcionista])
Return
SUMX(FILTER(ALL(Tbl_AporteAcionista), [nAcionista]=currAcionista&&[SPE]=currSPE&&[Data]=currDate),[Aporte])

 

Need Spend =
CALCULATE([Previsao(Data&SPE)],VALUES(Tbl_AcionistaEmpresa[SPE]))* MAX([Participacao])

 

Result:

 

12.pngCapture3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

DiogoValentim
New Member

Re: Help creating two mesures

Thx @v-shex-msft,

 

I needed to do some minor ajustments, but it worked!

 

Regards,

Diogo Valentim

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors