Forum Discussion

alanphamvistra4's avatar
alanphamvistra4
Frequent Visitor
3 years ago
Solved

Create Custom Column using PQL IF Statement Logic to Compare A Date to the Current Date

I need a PQL script to create a new column that essentially does the following for each entry   If [Expiration Date} is before [Current Date], then return "Expired" If [Expiration Date} is within ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi alanphamvistra4 ,

    You can follow the below methods to achieve it, please find the details in the attachment.

    1. By M query: add a custom column

    =if [Expiration Date]<Date.From(DateTime.LocalNow()) then "Expired" 
else if [Expiration Date]>=Date.From(DateTime.LocalNow()) and [Expiration Date]<=Date.AddDays(Date.From(DateTime.LocalNow()),30) then "Expiring" 
else "Active"

    2. By DAX: create a calculated column as below to get it

    Status = 
SWITCH (
    TRUE (),
    'Table'[Expiration Date] < TODAY (), "Expired",
    'Table'[Expiration Date] >= TODAY ()
        && 'Table'[Expiration Date]
            <= TODAY () + 30, "Expiring",
    "Active"
)

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi alanphamvistra4 ,

You can follow the below methods to achieve it, please find the details in the attachment.

1. By M query: add a custom column

=if [Expiration Date]<Date.From(DateTime.LocalNow()) then "Expired" 
else if [Expiration Date]>=Date.From(DateTime.LocalNow()) and [Expiration Date]<=Date.AddDays(Date.From(DateTime.LocalNow()),30) then "Expiring" 
else "Active"

2. By DAX: create a calculated column as below to get it

Status = 
SWITCH (
    TRUE (),
    'Table'[Expiration Date] < TODAY (), "Expired",
    'Table'[Expiration Date] >= TODAY ()
        && 'Table'[Expiration Date]
            <= TODAY () + 30, "Expiring",
    "Active"
)

Best Regards