Formula not filter date selected from slicer

[siewwei]

Below formula didnt filter records for condition " IFCA_AR_Trx_All[CreateDate] = [latest selected date]".

TodayProgressBilling(Buyer) =

SUMX(

    FILTER(IFCA_AR_Trx_All,

        IFCA_AR_Trx_All[Element Code] = "Progress Billing" &&

        NOT(ISBLANK(IFCA_AR_Trx_All[Document No])) &&

        IFCA_AR_Trx_All[Buyer Financier Billing] = "Buyer" &&

        IFCA_AR_Trx_All[CreateDate] = [latest selected date]

    ),

    IFCA_AR_Trx_All[DocumentAmt]

) 

latest selected date = LASTDATE('CreateDate Calendar'[Create Date]) <-- is a date selected from a slicer

CreateDate Calendar = CALENDAR(DATE(1960, 1, 1), TODAY()+365)

However i changed formula to below, then it works. May I know what's wrong with above formula? both  IFCA_AR_Trx_All[CreateDate] and [Create Date]  set as Date format dd/MM/yyyy

Today Progress Billing (Buyer) =

CALCULATE(

    [Document Amt],

    FILTER(

        ALL(IFCA_AR_Trx_All[Element Code], IFCA_AR_Trx_All[Document No], IFCA_AR_Trx_All[Buyer Financier Billing]),

        IFCA_AR_Trx_All[Element Code] = "Progress Billing" &&

        NOT(ISBLANK(IFCA_AR_Trx_All[Document No])) &&

        IFCA_AR_Trx_All[Buyer Financier Billing] = "Buyer"

    ),

    FILTER(

        ALL(IFCA_AR_Trx_All[CreateDate]),

        IFCA_AR_Trx_All[CreateDate] = [latest selected date]

    )

) 

[v-tangjie-msft]

Hi @siewwei ,

Please try adding the ALL function to the first formula.

TodayProgressBilling(Buyer) =
SUMX(
    FILTER(ALL(IFCA_AR_Trx_All),
        IFCA_AR_Trx_All[Element Code] = "Progress Billing" &&
        NOT(ISBLANK(IFCA_AR_Trx_All[Document No])) &&
        IFCA_AR_Trx_All[Buyer Financier Billing] = "Buyer" &&
        IFCA_AR_Trx_All[CreateDate] = [latest selected date]
    ),
    IFCA_AR_Trx_All[DocumentAmt]
) 

Please refer to the following documents for more information.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

How to Use ALL in a DAX Expression in Power BI - RADACAD

Best Regards,

Neeko Tang

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

[siewwei]

it return no value (blank) to me with above formula

[v-tangjie-msft]

Hi @siewwei ,

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang