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 Inven...
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. 🙂

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. 🙂