Forum Discussion

cristiannt's avatar
cristiannt
Frequent Visitor
9 years ago

Percentage from previous row

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' an...
Need Help
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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's avatar
cristiannt
Frequent Visitor
9 years ago

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's avatar
    Vvelarde
    Community Champion
    9 years ago

    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's avatar
      Vvelarde
      Community Champion
      9 years ago

      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
    )