Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 30 days after [Current Date], then return "Expiring"
Else "Active"
I have been playing around with M and DAX languages and can't figure out how to properly return "Expiring" if an entry will become expired in less then 30 days but is still technically Active since it has not expired yet.
Solved! Go to Solution.
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
74 | |
63 | |
51 | |
47 |
User | Count |
---|---|
218 | |
86 | |
64 | |
63 | |
60 |