Retornar Saldos de Abertura e Final quando ha multiplas transacoes no mesmo dia

[gustavohmotta]

Fala galera,

Estou criando um relatorio com o objetivo de ter uma visualizacao do extrato bancario porem filtrando por empresa. Por exemplo: tenho mais ou menos 100 empresas no grupo, e cerca de 180 contas bancarias, e preciso classifica-las em 3 categorias diferentes. Categoria A, B e C.

Quero fazer um drill down comecando em Categoria > Empresas > Contas Bancarias.

Como resultado final, quero ter as seguintes informacoes (ainda falta eu adicionar os slicers pra Categoria, mas acho que da pra entender):

 

Ai que vem o desafio. A tabela que eu consigo extrair do banco, tem as seguintes colunas:

Como voces podem ver na screenshot do meu dash, ha multiplas transacoes para o mesmo dia. Quero criar um cartao com o Saldo Anterior (Opening Balance CARD), que devera mostrar o saldo de abertura em 28/12 e Saldo final (Closing Balance CARD), que devera mostrar o valor de fechamento em 30/12, nesse exemplo.

 

De acordo com a minha formula, se eu tiver apenas UMA transacao no dia do valor de abertura, ela funciona. Mas se eu tiver multiplas transacoes nesse mesmo dia, ela esta somando o valor de abertura. Entendo que seja devido ao SUMX que usei, porem nao vejo outra alternativa e estou travado nessa missao kkkk.

Opening Balance CARD =

VAR vOpeningSelectedDate = MIN(f_banktransactions[Transaction Date])

VAR vSelectedAccounts = VALUES(f_banktransactions[Account])

RETURN

CALCULATE(

    SUMX(f_banktransactions, f_banktransactions[Balance] - f_banktransactions[Amount]),

        f_banktransactions[Transaction Date] = vOpeningSelectedDate &&

        f_banktransactions[Account] IN vSelectedAccounts

    )

 

Closing Balance CARD =

VAR vClosingSelectedDate = MAX(f_banktransactions[Transaction Date])

VAR vSelectedAccounts = VALUES(f_banktransactions[Account])

RETURN

CALCULATE(

    SUMX(f_banktransactions, f_banktransactions[Balance]),

        f_banktransactions[Transaction Date] = vClosingSelectedDate &&

        f_banktransactions[Account] IN vSelectedAccounts

    )

Qualquer ajuda sera muito bem vinda!! 

Obrigado!

G

Editado Março 15 por gustavohmotta

[Bruno Abdalla de Souza]

Opening Balance = 
VAR vSelectedAccounts = VALUES(dim_bankaccountmapping[Account])
VAR vOpening =
    SUMX(
        vSelectedAccounts,
        VAR vFirstIndex  =
            CALCULATE(
                MIN(f_banktransactions[Transaction ID]),
                ALL(dim_Calendar)
            )    
        VAR vOpeningBalance = 
            CALCULATE(
                SUM(f_banktransactions[Opening Balance]),
                f_banktransactions[Transaction ID] = vFirstIndex,
                ALL(dim_Calendar)
            )
        RETURN
        vOpeningBalance
    )
VAR vMinDate = MIN(dim_Calendar[Date])
VAR vAmount = 
    CALCULATE(
        SUM(f_banktransactions[Amount]),
        FILTER(
            ALL(dim_Calendar),
            dim_Calendar[Date] < vMinDate
        )
    )
VAR vResult = vOpening + vAmount
RETURN
vResult

 

E Closing Balance:

Closing Balance = 
[Opening Balance] + [Deposits] + [Withdrawals]

 

[Edson Igari]

@gustavohmotta, tudo bem? O que precisa na verdade não é tão complexo, a questão é que você precisar ter um valor para identificar qual é a primeira entrada e qual é a última, ou seja, um ID único para cada registro ou a hora do registro, etc. Só com a data fica difícil identificar qual foi o primeiro ou o último.

[gustavohmotta]

Fala @Edson Igari obrigado pela dica. Eu adicionei a index column, mas agora nao consigo criar uma formula pra retornar o valor de abertura (Balance - Amount) baseado no MENOR index respeitando a Transaction Date e Account que ja tenho no meu dashboard. Fiz uma tabelinha basica no Excel abaixo pra ver se explica melhor. Se matar essa charada o chopp eh na minha conta kkk

[Joao Raulino]

Suave @gustavohmotta?

Cria uma medida [MinIndex] e uma [MaxIndex]:

Citar

MinIndex =

CALCULATE(

    MIN(f_banktransactions[Index]),

    ALLEXCEPT(f_banktransactions, f_banktransactions[Account])

)

Citar

MaxIndex =

CALCULATE(

    MAX(f_banktransactions[Index]),

    ALLEXCEPT(f_banktransactions, f_banktransactions[Account])

)

E usa ela dentro de uma FILTER() nas tuas medidas dos cards:

Citar

Opening Balance CARD =

VAR vOpeningSelectedDate = MIN(f_banktransactions[Transaction Date])

VAR vSelectedAccounts = VALUES(f_banktransactions[Account])

RETURN

CALCULATE(

    SUMX(f_banktransactions, f_banktransactions[Balance] - f_banktransactions[Amount]),

    FILTER(

        ALL(f_banktransactions),

        f_banktransactions[Account] IN vSelectedAccounts &&

        f_banktransactions[Index] = [MinIndex]

    )

)

Citar

Closing Balance CARD =

VAR vOpeningSelectedDate = MAX(f_banktransactions[Transaction Date])

VAR vSelectedAccounts = VALUES(f_banktransactions[Account])

RETURN

CALCULATE(

    SUMX(f_banktransactions, f_banktransactions[Balance]),

    FILTER(

        ALL(f_banktransactions),

        f_banktransactions[Account] IN vSelectedAccounts &&

        f_banktransactions[Index] = [MaxIndex]

    )

)

E voila:

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

[gustavohmotta]

Opa @Joao Raulino obrigado pela ajuda!

Consegui chegar muito perto do objetivo com a sua sugestao. So precisei adaptar as formulas de MaxIndex e MinIndex pra incluir a TransactionDate tambem, assim como as formulas de Opening & Closing balance Card pro mesmo:

MinIndex =

CALCULATE(

    MIN(f_banktransactions[Transaction ID]),

    ALLEXCEPT(f_banktransactions, f_banktransactions[Account],f_banktransactions[Transaction Date])

)

 

Opening Balance CARD =

VAR vOpeningSelectedDate = MIN(f_banktransactions[Transaction Date])

VAR vSelectedAccounts = VALUES(f_banktransactions[Account])

RETURN

CALCULATE(

    SUMX(f_banktransactions, f_banktransactions[Balance] - f_banktransactions[Amount]),

    FILTER(

        ALL(f_banktransactions),

        f_banktransactions[Account] IN vSelectedAccounts &&

        f_banktransactions[Transaction Date] = vOpeningSelectedDate &&

        f_banktransactions[Transaction ID] = [MinIndex]

    )

)

 

Porem agora surgiu um novo problema: por exemplo, quando eu seleciono duas contas e uma delas nao tem transacao pro periodo especifico em que eu estou avaliando, meu Opening/Closing card nao refletem isso.

Exemplo abaixo:

Periodo analisado: 28/12 a 30/12.

Conta final 000: ha transacoes nos dias 28/12 e no dia 30/12. Sendo assim, meus cards retornam o valor de abertura e fechamento corretos:

 

Conta final 001: ha transacoes apenas no dia 29/12. Meus cards continuam funcionando, retornando o valor de abertura e fechamento corretos:

 

 

Ai que vem o desafio: quando eu seleciono as duas contas ao mesmo tempo, as formulas estao retornando apenas o opening/closing da conta com final 000, porque ela eh a unica, dentre as selecionadas, que possui transacoes no primeiro e ultimo dia da minha selecao (28 & 30/12, respectivamente):

 

 

Voce ja ganhou um chopp por minha conta pela resposta anterior, se ajudar nessa ganha outro kkkk. Pode mandar seu PIX por PM.

Obrigado @Joao Raulino

 

[gustavohmotta]

Pessoal, ainda estou travado com essa questao. Estou anexando o arquivo PBIX agora.

 

Preciso dos meus cartoes OPENING BALANCE, DEPOSITS, WITHDRAWALS e CLOSING BALANCE funcionando em ambas as paginas (Overview e Detailed Transactions).

Por exemplo, eu esperav que meu Opening Balance seria igual ao meu Closing balance no last year. Atualmente, OB = 10,976K vs Last Year's CB = 6,884k

 

 

Observacao: a coluna de TRANSACTION ID foi criada por mim no arquivo original (nao aparece nos steps desse arquivo PBIX pq eu tive que manipular por motivo de confidencialidade.

 

Se tiverem qualquer duvida pode me mandar mensagem no privado. Vou recompensar quem puder me ajudar primeiro.

Obrigado!Cash dashboard - Xperiun.pbix

[Joao Raulino]

Fala @gustavohmotta!

Antes de começar a resolver esse problema, nota só que a coluna [Transaction ID] que você criou para funcionar como índice não esta seguindo a ordem cronológica das transações:

Desta maneira o MaxIndex não vai corresponder a ultima transação do período selecionado, e portando os valores nunca vão bater.

Tenta resolver esse ponto ai antes da gente passar para o próximo passo 😉

Abs!

[gustavohmotta]

Em 05/04/2024 em 15:17, Joao Raulino disse:

Fala @gustavohmotta!

Antes de começar a resolver esse problema, nota só que a coluna [Transaction ID] que você criou para funcionar como índice não esta seguindo a ordem cronológica das transações:

Desta maneira o MaxIndex não vai corresponder a ultima transação do período selecionado, e portando os valores nunca vão bater.

Tenta resolver esse ponto ai antes da gente passar para o próximo passo 😉

Abs!

Fala Joao,

 

Na verdade, a coluna de Transaction ID segue a ordem cronologica de acordo com a Account. Se vc ordenar somente pela data, realmente parece nao seguir uma ordem correta, mas antes disso precisa levar em consideracao a Account tambem.

 

 

[gustavohmotta]

Em 15/04/2024 em 09:58, Bruno Abdalla de Souza disse:

Opening Balance = 
VAR vSelectedAccounts = VALUES(dim_bankaccountmapping[Account])
VAR vOpening =
    SUMX(
        vSelectedAccounts,
        VAR vFirstIndex  =
            CALCULATE(
                MIN(f_banktransactions[Transaction ID]),
                ALL(dim_Calendar)
            )    
        VAR vOpeningBalance = 
            CALCULATE(
                SUM(f_banktransactions[Opening Balance]),
                f_banktransactions[Transaction ID] = vFirstIndex,
                ALL(dim_Calendar)
            )
        RETURN
        vOpeningBalance
    )
VAR vMinDate = MIN(dim_Calendar[Date])
VAR vAmount = 
    CALCULATE(
        SUM(f_banktransactions[Amount]),
        FILTER(
            ALL(dim_Calendar),
            dim_Calendar[Date] < vMinDate
        )
    )
VAR vResult = vOpening + vAmount
RETURN
vResult

 

E Closing Balance:

Closing Balance = 
[Opening Balance] + [Deposits] + [Withdrawals]

 

Muito obrigado pela ajuda e dicas em nossas reunioes, Bruno!

Com certeza nos falaremos mais vezes.

Fica com Deus!