cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Saldo anterior

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:

C1.png

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.

C2.png

 

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

Muchas gracias.

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

1 ACCEPTED SOLUTION
Community Support
Community Support

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])

Capture11.JPG

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])

Capture12.JPG

 

 

 

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.

View solution in original post

4 REPLIES 4
Community Support
Community Support

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])

Capture11.JPG

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])

Capture12.JPG

 

 

 

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.

View solution in original post

Great @v-juanli-msft !

Thank you very much for your detailed answer.

Community Support
Community Support

Hi @Raul 

Would you like this result?

Capture8.JPGCapture9.JPG

 

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.

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:

C3.PNG

Any idea?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors