Percentage from previous row

[cristiannt]

Hey! How you doing guys?

I'd like to know the calculation for a measure that should have the following behaviour:

It should calculate the percentage between the actual number of 'Solicitudes' and the amount of 'Solicitudes' that the previous row had.

The table should show as following:

Estado - Solicitudes - %From previous

1             20                0

2             20                100

3             10                50

4             5                  50

Is there any way from DAX to get this done?

Thank you!

[tringuyenminh92]

Hi @cristiannt,

• Create calculated column to compute previous value:

Prev = CALCULATE(SUM(data[Solicitudes]),FILTER(data,data[Estado]=EARLIER(data[Estado])-1))

• Create calculated column to compute %

% = DIVIDE(data[Solicitudes],data[Prev])

[cristiannt]

Thank you all for your responses!

Hey, @tringuyenminh92 I'm getting the following error: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." Could you please support please?

@VvelardeThis one is ok, but I have a date filter, when I apply it, the calculation is dividing by ALL Solicitudes instead of the amount of Solicitudes that actually have with that filter. Is there a way to apply this? Thank you again guys!

[Vvelarde]

@cristiannt

Instead of All you can use AllSelected

[cristiannt]

@Vvelarde

Thank you again mate! But with AllSelected I'm having the same result.

My formula is now this one:

Previo =
VAR EstadoPrev = CambiosEstado[ID_Estado] - 1
RETURN
DIVIDE (
CALCULATE ( ( CambiosEstado[Total_Solicitudes]) ),
CALCULATE (
CambiosEstado[Total_Solicitudes],
FILTER ( ALLSELECTED(CambiosEstado), CambiosEstado[ID_Estado] = EstadoPrev )
)
)

 

ID_Estado 2, 3 and 4 should have 1,00 Previo, and ID_Estado 5 should have 0,44. Now this is dividing all by 32, which is the total amount of Solicitudes.

Thank you!

[Vvelarde]

hi @cristiannt

Use This Measure:

PrevioM% =
VAR EstadoPrev =
    MIN ( CambiosEstado[Estado] ) - 1
RETURN
    IF (
        HASONEVALUE ( CambiosEstado[Estado] ),
        DIVIDE (
            CALCULATE ( SUM ( CambiosEstado[Solicitudes] ) ),
            CALCULATE (
                SUM ( CambiosEstado[Solicitudes] ),
                FILTER (
                    ALLEXCEPT ( CambiosEstado; CambiosEstado[Fecha] ),
                    CambiosEstado[Estado] = EstadoPrev
                )
            )
        )
            + 0
    )

 

[cristiannt]

Thanks Mate @Vvelarde, but I'm still getting problems 😞 When I'm filtering by Fecha_Ingreso, this is still dividing by 32 Solicitudes

My measure right now is:

PrevioM% = 
VAR EstadoPrev =
    MIN ( CambiosEstado[ID_Estado] ) - 1
RETURN
    IF (
        HASONEVALUE ( CambiosEstado[ID_Estado] ),
        DIVIDE (
            CALCULATE ( DISTINCTCOUNT( CambiosEstado[ID_Solicitud] ) ),
            CALCULATE (
                DISTINCTCOUNT( CambiosEstado[ID_Solicitud] ),
                FILTER (
                    ALLEXCEPT ( CambiosEstado, CambiosEstado[Fecha_Ingreso].[Date] ),
                    CambiosEstado[ID_Estado] = EstadoPrev
                )
            )
        )
            + 0
    )

I'm using a distinct count since the intention of the report is to check how many Solicitudes were passing though the Estados, so I'm forced to use this calculation.

Thank you

[Vvelarde]

@cristiannt

Hi, maybe you have a different structure or data model that i can't see.

If you want contact me by PM to send me your file with anonimize data or upload your PBIX and share it.

[Vvelarde]

The measure finally look like this:

%vsPrevio =
VAR EstadoPrev =
    MIN ( CambiosEstado[ID_Estado] ) - 1
VAR MINFECHA =
    CALCULATE (
        MIN ( CambiosEstado[Fecha_Ingreso] ),
        ALLSELECTED ( CambiosEstado )
    )
VAR MAXFECHA =
    CALCULATE (
        MAX ( CambiosEstado[Fecha_Ingreso] ),
        ALLSELECTED ( CambiosEstado )
    )
RETURN
    IF (
        HASONEVALUE ( CambiosEstado[ID_Estado] ),
        DIVIDE (
            CALCULATE ( DISTINCTCOUNT ( CambiosEstado[ID_Solicitud] ) ),
            CALCULATE (
                DISTINCTCOUNT ( CambiosEstado[ID_Solicitud] ),
                FILTER (
                    ALL ( CambiosEstado ),
                    CambiosEstado[ID_Estado] = EstadoPrev
                        && CambiosEstado[Fecha_Ingreso] >= MINFECHA
                        && CambiosEstado[Fecha_Ingreso] <= MAXFECHA
                )
            )
        )
            + 0
    )

[Vvelarde]

@cristiannt

Using your sample data you can use a calculated column:

Previo =
VAR EstadoPrev = Table1[Estado] - 1
RETURN
    DIVIDE (
        CALCULATE ( VALUES ( Table1[Solicitudes] ) ),
        CALCULATE (
            VALUES ( Table1[Solicitudes] ),
            FILTER ( ALL ( Table1 ), Table1[Estado] = EstadoPrev )
        )
    )

 Or you can use the next measure:

PrevioM =
VAR EstadoPrev =
    MIN ( Table1[Estado] ) - 1
RETURN
    IF (
        HASONEVALUE ( Table1[Estado] ),
        DIVIDE (
            CALCULATE ( SUM ( Table1[Solicitudes] ) ),
            CALCULATE (
                SUM ( Table1[Solicitudes] ),
                FILTER ( ALL ( Table1 ), Table1[Estado] = EstadoPrev )
            )
        )
            + 0
    )