I’d like to compare the below columns to get the results under Valid/Expired, below is the Format used for the columns:
| Item | Format | Found In |
|---|---|---|
| Age | Number | Calculated Measure |
| Validity Period | Text | Merged Column From Source |
| Expiry | Date | Merged Column From Source |
| Transaction End of Period | Date | Calculated Measure |
Input:
| Age | Validity Period | Expiry | Transaction End of Period |
|---|---|---|---|
| 2.42 | Not Specified | 03/31/2025 | 04/30/2024 |
| 0.00 | 1.00 | 03/31/2025 | 04/30/2024 |
| 3.10 | 3.00 | 03/31/2025 | 04/30/2024 |
| 1.09 | Indefinite | 03/31/2025 | 04/30/2024 |
| 4.38 | Not Applicable | 12/31/9999 | 04/30/2024 |
I use this Excel formula to get the “Valid/Expired” column, but I’m unable to replicate it in Power BI.
IF(OR([@[Expiry]]<[@[Transaction End of Period]],[@[Age]]>=[@Validity Period]),”Expired”,”Valid”)
Output:
| Age | Validity Period | Expiry | Transaction End of Period | Valid/Expired |
|---|---|---|---|---|
| 2.42 | Not Specified | 03/31/2031 | 04/30/2024 | Valid |
| 0.50 | 1.00 | 03/31/2024 | 04/30/2024 | Expired |
| 3.10 | 3.00 | 02/29/2024 | 04/30/2024 | Expired |
| 1.09 | Indefinite | 03/31/2025 | 04/30/2024 | Valid |
| 4.38 | Not Applicable | 12/31/9999 | 04/30/2024 | Valid |
Note that the data is not the actual data, I’ve just placed dummy data close enough to replicate
I am open to either transforming the data or using calculated measures.
Thank you for the help!