Nested conditional does not work

[DanCasSan]

Hello partners,

I have generated a measure that adds the purchase values, but this depends on the filter that is made (Year, Quarter and Month). I have validated that this metric works when the Quarter is filtered, but not when the month is filtered, the values ​​come out blank, any tips to fix this?

Medida = 
VAR TriSel = IF(ISFILTERED(Calendario[Flag Trimestre]),1,0)
VAR MesSel = IF(ISFILTERED(Calendario[Mes]),1,0)
VAR MaxAnio = MAX(Calendario[Año])
VAR AnioAnt = MAX(Calendario[Año])-1
VAR TrimAnt = SELECTEDVALUE(Calendario[Flag Trim])
VAR MesAnt = IF(DATEDIFF(STARTOFYEAR(Calendario[Fecha],"12/31"),MAXX(ALLSELECTED(Calendario),Calendario[Fecha]),MONTH)=0,12,
    DATEDIFF(STARTOFYEAR(Calendario[Fecha],"12/31"),MAXX(ALLSELECTED(Calendario),Calendario[Fecha]),MONTH))
RETURN
IF(ISFILTERED(Calendario[Año]),
    IF(TriSel=1 && MesSel=0,
        IF(SELECTEDVALUE(Calendario[Flag Trimestre])="Trim. 1",
            CALCULATE(SUM(TBL_Consolidada[Valor de Compra]),Calendario[Año]=AnioAnt,Calendario[Flag Trimestre]=TrimAnt,
                KEEPFILTERS(TBL_Consolidada[Descripción Portafolio] IN {"CAJA","RENTA FIJA","RENTA VARIABLE","DESPÓSITOS Y EQUIVALENTES"})),
            CALCULATE(SUM(TBL_Consolidada[Valor de Compra]),Calendario[Flag Trimestre]=TrimAnt,
                KEEPFILTERS(TBL_Consolidada[Descripción Portafolio] IN {"CAJA","RENTA FIJA","RENTA VARIABLE","DESPÓSITOS Y EQUIVALENTES"}))
        ),
        IF(TriSel=1 && MesSel=0,
            IF(SELECTEDVALUE(Calendario[Nro Mes])=1,
                CALCULATE(SUM(TBL_Consolidada[Valor de Compra]),Calendario[Año]=AnioAnt,Calendario[Nro Mes]=MesAnt,
                    KEEPFILTERS(TBL_Consolidada[Descripción Portafolio] IN {"CAJA","RENTA FIJA","RENTA VARIABLE","DESPÓSITOS Y EQUIVALENTES"})),
                CALCULATE(SUM(TBL_Consolidada[Valor de Compra]),Calendario[Nro Mes]=MesAnt,
                    KEEPFILTERS(TBL_Consolidada[Descripción Portafolio] IN {"CAJA","RENTA FIJA","RENTA VARIABLE","DESPÓSITOS Y EQUIVALENTES"}))
            ),
            CALCULATE(SUM(TBL_Consolidada[Valor de Compra]),Calendario[Año]=MaxAnio,Calendario[Nro Mes]=MesAnt,
                KEEPFILTERS(TBL_Consolidada[Descripción Portafolio] IN {"CAJA","RENTA FIJA","RENTA VARIABLE","DESPÓSITOS Y EQUIVALENTES"})))))

Thank you!

[Anonymous]

HI @DanCasSan,

Did these suggestions help with your scenario? If not, you can feel free to post here with detailed information.
Regards,

Xiaoxin Sheng

[Anonymous]

HI @DanCasSan,

It tried to simply your formulas and I found your issue may cause by the conditions. 

Medida =
VAR MaxAnio =
    MAX ( Calendario[Año] )
VAR TrimAnt =
    SELECTEDVALUE ( Calendario[Flag Trim] )
VAR diff =
    DATEDIFF (
        STARTOFYEAR ( Calendario[Fecha], "12/31" ),
        MAXX ( ALLSELECTED ( Calendario ), Calendario[Fecha] ),
        MONTH
    )
VAR MesAnt =
    IF ( diff = 0, 12, diff )
RETURN
    IF (
        ISFILTERED ( Calendario[Año] ),
        CALCULATE (
            SUM ( TBL_Consolidada[Valor de Compra] ),
            FILTER (
                Calendario,
                IF (
                    ISFILTERED ( Calendario[Flag Trimestre] ) && ISFILTERED ( Calendario[Mes] ),
                     (
                        IF (
                            SELECTEDVALUE ( Calendario[Flag Trimestre] ) = "Trim. 1",
                            Calendario[Año] = MaxAnio - 1
                        )
                            && Calendario[Flag Trimestre] = TrimAnt
                    )
                        || (
                            IF ( SELECTEDVALUE ( Calendario[Nro Mes] ) = 1, Calendario[Año] = MaxAnio - 1 )
                                && Calendario[Nro Mes] = MesAnt
                        ),
                    Calendario[Año] = MaxAnio
                        && Calendario[Nro Mes] = MesAnt
                )
            ),
            KEEPFILTERS ( TBL_Consolidada[Descripción Portafolio]
                IN { "CAJA", "RENTA FIJA", "RENTA VARIABLE", "DESPÓSITOS Y EQUIVALENTES" } )
        )
    )

You have set the same level conditions to compare the current 'Flag Trimestre' and 'Nro Mrs' fields, it only goes to the front part and not trigger the second comparison. 

BTW, selectedvalue functions also not work on all row contents levels, if you do not add processing to deal with multiple values, it will return blank when you work on summary row contents.

Regards,

Xiaoxin Sheng

[amitchandak]

@DanCasSan , real complex formula. What exactly you are trying to achieve here.

Can you share  a sample pbix after removing sensitive data.

[DanCasSan]

Hello @amitchandak ,

I have uploaded a model of the file in the following path: https://comunidadupnedu-my.sharepoint.com/:u:/g/personal/n00062908_upn_pe/EfY6qxRFwEFNsVza87W8r34BlO...

What is needed is that when the quarter is filtered, the sum of the purchase value is from the previous quarter, if the month filter is used, the sum must be based on the month before the selected one.

Note: In the quarter filter there is only the maximum month, that is, quarter 2 is comprised of the month of June, quarter 4 will be December.

Reggards,

DC