DAX Filter not working as expected

[JS_UNI4C]

Hi all, 

Hoping someone can help as I am a little stumped as to why this ISN'T working. 

I am busy creating a sales dashboard but wanting the dashboard to be a little smart. I have 2 boxes being YTD and MTD. I created a slicer that is based off a 'calendar for sales' table which is in turn linked to the relevant sales data table called "ValueEntries". I created a separate 'calendar slicer' table which I linked to the 'calendar for sales' table. 

What I am trying to achieve:

• YTD box shows the sum of sales for the up to the selected month in the slicer
• MTD box shows the sum of sales for the month selected in the slicer

Example: If I select 202404 in the slicer, the MTD box should show April 2024 sales and the YTD box should show the sum of Jan 2024 to April 2024. Currently, the latter YTD portion simply isn't working and I can't quite work out why.

The MTD slicer works perfectly. When nothing is selected in the slicer, then is shows the current month's sales ie it is current June 4, 2024 so it shows the sales so far in Jun for 2024. The problem comes in with the YTD result as it only shows the months sales instead of the sum of all sales for the year up to the month selected. 

My DAX code is as follows:

MTD

Revenue CY MTD = CALCULATE([SalesSum],FILTER('Calendar for sales',

'Calendar for sales'[MonthYearNum] = MAX('Calendar slicer'[MonthYearNum])

&&

'Calendar for sales'[Year] = MAX('Calendar slicer'[Year])))

YTD

Revenue CY MTD = CALCULATE([SalesSum],FILTER('Calendar for sales',

'Calendar for sales'[MonthYearNum] <= MAX('Calendar slicer'[MonthYearNum])

&&

'Calendar for sales'[Year] = MAX('Calendar slicer'[Year])))

The only difference between the two codes are the "<" indicated above in red.

Not sure if it is relevant or not but the measure SalesSum is made up as follows:

SalesSum = [SalesSumCM] + [SalesSumINV]

SalesSumCM = SUMX(filter(ValueEntries,ValueEntries[Document_Type] = "Sales Credit Memo"),ValueEntries[Sales_Amount_Actual])

SalesSumINV = SUMX(filter(ValueEntries,ValueEntries[Document_Type] = "Sales Invoice"),ValueEntries[Sales_Amount_Actual])

[Anonymous · Accepted solution]

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.

[Greg_Deckler]

@JS_UNI4C Try this: Better Year to Date Total - Microsoft Fabric Community

[JS_UNI4C]

Hi Greg, 

Thanks but I'm not sure that is going to work or be easy to adapt. If I read the DAX correctly, your sales table as a date column but also a year column. My data does not have this and as I am trying to keep the data as standard as possible, this would automatically rule this out.

My data is also an entire database so has both sales, purchases and adjustments which is why I have filters in my SalesSumINV and SalesSumCN measures. Your table appears to be only sales and so simply sums one column but in my data, this would result in the incorrect result.

[Greg_Deckler]

@JS_UNI4C It's easily adaptable. You can watch the video here on how to do it with just a Date column:

[JS_UNI4C]

Thanks Gary but I am simply struggling too much to adapt it. You are far more knowledgeable than me when it comes to this so for you it may seem simple but for me it isn't. Your videos have too much prior knowledge required and I'm afraid I don't have the luxury of time to watching all your videos and then still try and figure them out. I watched the above video but you use helper columns as you put it yourself which means I have to go modify my data and I am trying to stear clear of that. You also have some measures in there which are not shown e.g. [Internet sales]. 

I apologise, I don't meant to sound rude, but I don't think your suggestion is what I am after. I was hoping someone could tell me what I am doing wrong with my DAX instead of having to try create what feels like an entire new file.