cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heitorchehad
Regular Visitor

Cashflow result in next month, with multiples relationships

Hello!

I would like a help, I have several queries that bring me the information via SQL, they are all related to a table of FINANCIAL CATEGORY.

I have the Reconcilable Bank Balances to date today, next month, when there is no value in the Balances table, I want the flow to return the result of the previous month.

Something similar to this formula, which does not work because of the relation of the tables, which return only where there are values ​​filled in the financial category.

TEST MEASURE = var resultPrevious = SUMX ('Movement Categories'; CALCULATE ([Result Flow]; PREVIOUSMONTH (Dale)
var Previous Balance = SUMX ('BANK AND ACCOUNT'; CALCULATE ([Previous Balance]; PREVIOUSDAY (FIRSTDATE (DCalendario [Date]))))
var Input = CALCULATE (sum (INPUTS [Value Dep])) + [Trans. Entry] + [Accounts Receivable]
var Output = CALCULATE (sum ('OUTPUTS' [TotalLiq_des])) - [Trans. Exit] - [Accounts Payable]
return
IF (HASONEVALUE ('Movement Categories' [CMF]);
SWITCH (VALUES ('Movement Categories' [CMF]);
"BANK BALANCE"; IF (FIRSTDATE [Date]) <TODAY (); Previous Balance; resultPrevious+input-ouput);
[Flow Result]))

 

 

Capture.PNGtempsnip.png

4 REPLIES 4
heitorchehad
Regular Visitor

Re: Cashflow result in next month, with multiples relationships

Anyone could help me?

Community Support
Community Support

Re: Cashflow result in next month, with multiples relationships

@heitorchehad,

 

You may try CROSSFILTER Function outside and set cross-filter direction to None.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
heitorchehad
Regular Visitor

Re: Cashflow result in next month, with multiples relationships

@v-chuncz-msft


I'm thankful for your help, but doesnt work.
The tables: Inputs, Outputs, Receivables, Payments, Bank Account, Bank Transfers (In and Out) have values, and that querys is in relationship with my FINANCIALS CATEGORYs.


I need the cashflow result of previousmonth will be inputed on the line of 'Saldo Bancário Atual', so, when i use the measures for calculate the result and put them in the line, they don't bring me nothing, because don't have any values in this specific line.

 

I'm thinking in do a SUMMARIZE to calculate that result in a another table, so when i do that i got other problemas, with my filters of report, in that SUMMARIZE i cant do the filters for my companies.

 

Maybe a SUMMARIZE of each table? I dont think this is a good idea..

heitorchehad
Regular Visitor

Re: Cashflow result in next month, with multiples relationships

In theory i need basicly that:

MEDIDA TESTE = VAR RESULTADOANTERIOR = SUMX('CATEGORIAS DE MOVIMENTACAO';CALCULATE([RESULTADO FLUXO];PREVIOUSMONTH(DCALENDARIO[DATE])))
                VAR SALDOANTERIOR = SUMX('BANCO E CONTA'; CALCULATE([SALDO ANTERIOR];PREVIOUSDAY(FIRSTDATE(DCALENDARIO[DATE]))))
                VAR ENTRADA = CALCULATE(SUM(ENTRADAS[VALOR DEP]))+[TRANSF. ENTRADA]+[CONTAS A RECEBER]
                VAR SAIDA = CALCULATE(SUM('SAÍDAS'[TOTALLIQ_DES]))-[TRANSF. SAIDA]-[CONTAS A PAGAR]       
                        RETURN
                       IF(HASONEVALUE('CATEGORIAS DE MOVIMENTACAO'[CMF]);
                            SWITCH(VALUES('CATEGORIAS DE MOVIMENTACAO'[CMF]);
                                    "SALDO BANCÁRIO"; IF(FIRSTDATE(DCALENDARIO[DATE])<TODAY();SALDOANTERIOR;RESULTADOANTERIOR+ENTRADA-SAIDA);
                                    
                                   [RESULTADO FLUXO]))

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.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors