Forum Discussion

JS_UNI4C's avatar
JS_UNI4C
Icon for Helper I rankHelper I
2 years ago
Solved

DAX Filter not working as expected

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's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

5 Replies

    • JS_UNI4C's avatar
      JS_UNI4C
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.