Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
siewwei
Frequent Visitor

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]
    )
3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

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. 

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.