Saldo anterior

[Raul]

Hola a todos!

Tengo una tabla para representar en una visualización de matriz:

Hello everyone!
I have a table to represent in a matrix visualization:

Necesito encontrar como sería la medida S (Saldo) para que me muestre el valor deseado, este es:

Sumatorio del campo Importe de todos los registros anteriores a la fecha inicial del periodo especificado (por un filtro) + el valor de la columna D - el valor de la columna H.

I need to find what the S (Balance) measure would be like to show me the desired value, this is:
Sum of the field Amount of all records prior to the initial date of the specified period (by a filter) + the value of column D - the value of column H.

¿Cómo sería la fórmula de esta medida?

Muchas gracias.

What would the formula of this measure be like?
Thank you.

[v-juanli-msft · Accepted solution]

Hi @Raul 

Create a calculated  column

index = RANKX (

    FILTER('Table','Table'[doc]=EARLIER('Table'[doc])),

    RANKX ( FILTER('Table','Table'[doc]=EARLIER('Table'[doc])), [date],, ASC )

        + DIVIDE (

            RANKX (FILTER('Table','Table'[doc]=EARLIER('Table'[doc])),[no],, ASC ),

            COUNTROWS ('Table' ) + 1

        ),,ASC,Dense)

Create another table

Table 2 = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Don’t create relationship for this table

Add “year” and “month” from this table to the slicers

Create measures in “Table”

M_value = SUM('Table'[D])-SUM('Table'[H])

final output = SUMX(FILTER(ALLEXCEPT('Table','Table'[doc]),'Table'[index]<=MAX('Table'[index])),[M_value])

To make the visual only show selected month data, create a measure as below

Measure = IF(MAX('Table'[date])>=MIN('Table 2'[Date]),[final output])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Raul]

Great @v-juanli-msft !

Thank you very much for your detailed answer.

[v-juanli-msft]

Hi @Raul 

Would you like this result?

If so,

1. in edit queries, replace value null with 0, close&&apply

2. add a calculated column " index"

index = RANKX (
    FILTER('Table','Table'[doc]=EARLIER('Table'[doc])),
    RANKX ( FILTER('Table','Table'[doc]=EARLIER('Table'[doc])), [date],, ASC )
        + DIVIDE (
            RANKX (FILTER('Table','Table'[doc]=EARLIER('Table'[doc])),[no],, ASC ),
            COUNTROWS ('Table' ) + 1
        ),,ASC,Dense)

3. create a measure

Measure =
VAR n1 =
    CALCULATE (
        SUM ( 'Table'[D] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[doc] ), 'Table'[des] = "saldo anterior" )
    )
VAR n2 =
    CALCULATE (
        SUM ( 'Table'[D] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[doc] ),
            'Table'[des] <> "saldo anterior"
                && 'Table'[index] <= MAX ( 'Table'[index] )
        )
    )
VAR n3 =
    CALCULATE (
        SUM ( 'Table'[H] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[doc] ),
            'Table'[des] <> "saldo anterior"
                && 'Table'[index] <= MAX ( 'Table'[index] )
        )
    )
RETURN
    n1 + n2 - n3

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Raul]

Hi @v-juanli-msft  ,

Thanks a lot for your fast answer and great example but, it's not correct for me.

I don't have the row 'saldo anterior' in my table. In my table, I only have the rows with the values for the D and H column. The first image of my post it's a report from Excel and I need to transform into a Power BI visualization, like the second image. The value of the 'saldo anterior' (previous balance) must be a calculation of the Amount field in the table that contains the difference between the values in columns D and H for all records. If, for example, I am checking the month of Agost 2019, the visualization has to show the records of that month but column S has to calculate the sum of all the amounts before Agost 1, 2019 for each Tipo de doc ( BANC x) and accumulate the sum of the value D minus the value H.

That is:

Any idea?