Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Raul
Post Patron
Post Patron

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
v-juanli-msft
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
v-juanli-msft
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.

Great @v-juanli-msft !

Thank you very much for your detailed answer.

v-juanli-msft
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.