Search code examples
exceldaxpowerpivot

PowerPivot DAX formula if then for dates

I have a column named ExpireDate in my PowerPivot table (not PowerBI) and want to make another column based on the value of Today's Date - ExpireDate with a DAX formula like below:

=if(Format([ExpireDate] -today(), "General Number") <= 90, "Less than 3 months", 
 if(90 < Format( [ExpireDate] -today() ,"General Number") <= 180, " 3 to 6 months", 
 if([Format (ExpireDate]-today() ,"General Number") > 180, "More than 6 months","Other"))

But this formula keeps showing error messages, saying it needs a proper formula. Anybody knows how to deal with this problem? Thanks a lot.

Solution

  • Assuming you are using Excel 2016, and your table name is "Table":

    =
VAR Expiration = Table[ExpireDate] - TODAY ()
RETURN
    SWITCH (
        TRUE (),
        Expiration <= 90, "Less than 3 months",
        Expiration <= 180, " 3 to 6 months",
        Expiration > 180, "More than 6 months",
        "Other"
    )