Can't create date bucket with DAX if statement

[SM_1997_]

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 🙂

[bolfri · Accepted solution]

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_]

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]

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

[SM_1997_]

I realized that minutes after I got it working, I think I need a measure

[YukiK]

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_]

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.