DAX Filter not working as expected

Hi JS_UNI4C

Greg_Deckler Thank you very much for your prompt reply, please allow me to share some content here.

Based on the code you provided, it looks like you have three tables. The cause of your problem, I think, is that your tables don't have the right relationship with each other.

It is best to have a relationship between Calendar for sales and ValueEntries. For example, if both tables contain order ids, you can use the order ids to create relationship.

If you cannot create a relationship, you need MonthYearNum and Year in ValueEntries. For example:

"ValueEntries"

"Calendar slicer"

Create measures.

SalesSum = 
var SalesSumCM = SUMX(filter(ValueEntries,ValueEntries[Document_Type] = "Sales Credit Memo"),ValueEntries[Sales_Amount_Actual])
var SalesSumINV = SUMX(filter(ValueEntries,ValueEntries[Document_Type] = "Sales Invoice"),ValueEntries[Sales_Amount_Actual])
RETURN
SalesSumCM + SalesSumINV

MTD = 
IF(
ISFILTERED('Calendar slicer'[Year]) && ISFILTERED('Calendar slicer'[MonthYearNum]),
SUMX(
    FILTER(
        ALL('ValueEntries'),
        'ValueEntries'[Year] = MAX('Calendar slicer'[Year])
        &&
        'ValueEntries'[MonthYearNum] = MAX('Calendar slicer'[MonthYearNum])
    ),
    'ValueEntries'[SalesSum]
),
SUMX(
    FILTER(
        ALL('ValueEntries'),
        'ValueEntries'[Year] = YEAR(TODAY())
        && 
        'ValueEntries'[MonthYearNum] = MONTH(TODAY())
    ),
    'ValueEntries'[SalesSum]
)
)

YTD = 
SUMX(
    FILTER(
        ALL('ValueEntries'),
        'ValueEntries'[Year] = MAX('Calendar slicer'[Year])
        && 
        'ValueEntries'[MonthYearNum] <= MAX('Calendar slicer'[MonthYearNum])
    ),
    'ValueEntries'[SalesSum]
)

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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