Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

CALCULATE con FILTER

Good morning

I have an Excel query of amounts : measure1(BAT) and measure2(YTD)

on a tabular model,

with one filter per year and one filter per month.

In the DETAIL ROWS EXPRESSION field of measure1(MTD) I have this instruction :

SELECTCOLUMNS(V_TAB_FINANCIAL_CAL,

"Moviment",V_TAB_FINANCIAL_CAL[Entry No_],

"Data",V_TAB_FINANCIAL_CAL[Posting Date],

"Document",V_TAB_FINANCIAL_CAL[Document No_],

"Descripcio",V_TAB_FINANCIAL_CAL[Description],

"Account",V_TAB_FINANCIAL_CAL[G_L Account No_],

"UN",V_TAB_FINANCIAL_CAL[Unidad_Negocio_Name]

"CC",V_TAB_FINANCIAL_CAL[Centro_Coste_Name],

"Family",V_TAB_FINANCIAL_CAL[Familia_Name],

"conciliation",V_TAB_FINANCIAL_CAL[No_ Mov_ conciliation],

"Import",V_TAB_FINANCIAL_CAL[Amount]

)

So when you double click on measure1 (MTD) the detail of the movements of the month comes out correctly and with the selected fields.

For the measured case2(YTD) you should filter movements from the beginning of the filter year to the end of the filter month ¿ what would be the FILTER statement combined with the CALCULATE ¿

Thanks for the help

  • Syndicate_Admin's avatar
    Syndicate_Admin
    2 years ago

    Thanks for the reply

    Finally the option that has worked for me is this:

    CALCULATETABLE(
    SELECTCOLUMNS(
    V_TAB_FINANCIAL_CAL,

    "Moviment",V_TAB_FINANCIAL_CAL[Entry No_],
    "Data",V_TAB_FINANCIAL_CAL[Posting Date],
    "Document",V_TAB_FINANCIAL_CAL[Document No_],
    "Descripcio",V_TAB_FINANCIAL_CAL[Description],
    "Cuenta",V_TAB_FINANCIAL_CAL[G_L Account No_],
    "UN",V_TAB_FINANCIAL_CAL[Unidad_Negocio_Name],
    "CC",V_TAB_FINANCIAL_CAL[Centro_Coste_Name],
    "Familia",V_TAB_FINANCIAL_CAL[Familia_Name],
    "Conciliacio",V_TAB_FINANCIAL_CAL[No_ Mov_ Conciliacion],
    "Import",V_TAB_FINANCIAL_CAL[Amount]

    ),
    DATESYTD(V_TAB_CALENDARIO_CAL[DATE])
    )

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Syndicate_Admin 

    You can try to use the following measure

    Measure2 =
CALCULATE (
    [measure1],
    FILTER (
        ALLSELECTED ( V_TAB_FINANCIAL_CAL ),
        [Posting Date] <= MAX ( V_TAB_FINANCIAL_CAL[Posting Date] )
            && YEAR ( V_TAB_FINANCIAL_CAL[Posting Date] )
                = YEAR ( MAX ( V_TAB_FINANCIAL_CAL[Posting Date] ) )
    )
)

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    Thanks for the reply

    Finally the option that has worked for me is this:

    CALCULATETABLE(
    SELECTCOLUMNS(
    V_TAB_FINANCIAL_CAL,

    "Moviment",V_TAB_FINANCIAL_CAL[Entry No_],
    "Data",V_TAB_FINANCIAL_CAL[Posting Date],
    "Document",V_TAB_FINANCIAL_CAL[Document No_],
    "Descripcio",V_TAB_FINANCIAL_CAL[Description],
    "Cuenta",V_TAB_FINANCIAL_CAL[G_L Account No_],
    "UN",V_TAB_FINANCIAL_CAL[Unidad_Negocio_Name],
    "CC",V_TAB_FINANCIAL_CAL[Centro_Coste_Name],
    "Familia",V_TAB_FINANCIAL_CAL[Familia_Name],
    "Conciliacio",V_TAB_FINANCIAL_CAL[No_ Mov_ Conciliacion],
    "Import",V_TAB_FINANCIAL_CAL[Amount]

    ),
    DATESYTD(V_TAB_CALENDARIO_CAL[DATE])
    )