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

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.