Forum Discussion

siewwei's avatar
siewwei
Frequent Visitor
2 years ago

Formula not filter date selected from slicer

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]
    )
) 
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      siewwei
      Frequent Visitor
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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