Forum Discussion

SM_1997_'s avatar
SM_1997_
Frequent Visitor
3 years ago
Solved

Can't create date bucket with DAX if statement

Hi all!

 

I am trying to create a date bucket using DAX to filter date ranges from last 3 months, last 6 months, and previous year.

This is the code that I have:

Date Bucket =
IF('Vendor Inventory'[Date Received] >= TODAY() && 'Vendor Inventory'[Date Received] < MONTH(TODAY() -3) , "Last 3 Months",
IF('Vendor Inventory'[Date Received] >= TODAY() && 'Vendor Inventory'[Date Received] < MONTH(TODAY() -6), "Last 6 months",
IF('Vendor Inventory'[Date Received] >= TODAY() && 'Vendor Inventory'[Date Received] < YEAR(TODAY() -1), "Last Year", "Other")))
 
For some reason this isn't working. I get "Other" for every row. Does anyone know what could be wrong?
 
Many thanks 🙂
Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    TODAY() is 2023-03-02

     

    MONTH(TODAY() -3) means TODAY()-3, which is 2023-02-28 and a month from this is 2.

    MONTH(TODAY() -6) means TODAY()-6, which is 2023-02-25 and a month from this is also 2.

    YEAR(TODAY() -1) means TODAY()-1, which is 2023-03-01 and a year from this is 2023.

     

    'Vendor Inventory'[Date Received] >= TODAY() means "is my date received is equal or greater than today"

     

    So you are looking for todays() and futures dates that is also lower than a some NUMBER (not date). That's why all your rows are in Other bucket. There is no chanse that something will match to this condition. 🙂

6 Replies

  • YukiK's avatar
    YukiK
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    You don't need "'Vendor Inventory'[Date Received] >= TODAY() " logic. Tha's contradicting the other logic you have in the formula.

     

    In each IF statement, only keep 'Vendor Inventory'[Date Received] < MONTH(TODAY()) -3 part but change it to something like MONTH( 'Vendor Inventory'[Date Received] ) > MONTH(TODAY()) -3 && YEAR('Vendor Inventory'[Date Received]) = YEAR(TODAY()) 

     

    And for last year logic, you can do

    YEAR('Vendor Inventory'[Date Received]) = YEAR(TODAY()) -1

    • SM_1997_'s avatar
      SM_1997_
      Frequent Visitor
      3 years ago

      Thank you so much for your help but I'm afraid this isn't working. I'm looking to create date buckets where 

      'Vendor Inventory'[Date Received] is in todays date minus 3 months ago, todays date minus 6 months ago,and todays date minus 1 year ago.
       
      I realize that my original wording might have been confusing, sorry.
  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage
    3 years ago

    TODAY() is 2023-03-02

     

    MONTH(TODAY() -3) means TODAY()-3, which is 2023-02-28 and a month from this is 2.

    MONTH(TODAY() -6) means TODAY()-6, which is 2023-02-25 and a month from this is also 2.

    YEAR(TODAY() -1) means TODAY()-1, which is 2023-03-01 and a year from this is 2023.

     

    'Vendor Inventory'[Date Received] >= TODAY() means "is my date received is equal or greater than today"

     

    So you are looking for todays() and futures dates that is also lower than a some NUMBER (not date). That's why all your rows are in Other bucket. There is no chanse that something will match to this condition. 🙂

    • SM_1997_'s avatar
      SM_1997_
      Frequent Visitor
      3 years ago

      I figured it out with this:

       

      Date Bucket =
      IF('Vendor Inventory'[Date Received] > EDATE(TODAY(), -3), "Last 3 months",
      IF('Vendor Inventory'[Date Received] > EDATE(TODAY(), -6), "Last 6 months",
      IF('Vendor Inventory'[Date Received] > EDATE(TODAY(), -12), "Last Year", "Other")))
       
      Thanks for the help!
      • bolfri's avatar
        bolfri
        Icon for Solution Sage rankSolution Sage
        3 years ago

        It won't work. You will get insteed:
        Last 3 months

        Months between 4 and 6

        and months between 7 and 12

        This is how your code works. 🙂