Calcualate 3months/6months/12months out using Expiration Date

[beginner_user08]

Hello PowerBi Experts,

Could some please help with the PBI Query.

In my table, I have expiration date column and I wanted to know which among my records  are expiring in the next 3/6/12 months. My  Today's date here is 3/22/2023. Below is my criteria for the bucket of months. if contract is expiring in 0-90 days from today's date, then that is <3months; if 91-180 days, 6months, if 181-365, that is 12 months. Anything exceeding 12 months, will be ">12 months". Else (for contracts expiring before today's date, it would be "out of range").

how do i create this formula in Power BI. 

Record ID	Expiration Date	Datedif from Today	Comment
1	12/21/2022	-91	Out of Range
2	04/21/2023	30	<3months
3	05/06/2023	45	<3months
4	05/16/2023	55	<3months
5	06/20/2023	90	<6months
6	06/30/2023	100	<6months
7	09/23/2023	185	<12months
8	10/08/2023	200	<12months

My formula below is actually looking for past expiring dates and not future dates, so I cannot predict the accurate no of records expiring soon.

Expire Aging Bucket =

IF(AND(DATEDIFF('CLM Report'[Expires].[Date],TODAY(),DAY)>= 0,

DATEDIFF('CLM Report'[Expires].[Date],TODAY(),DAY)<=90), "3 Months",

IF(AND(DATEDIFF('CLM Report'[Expires].[Date],TODAY(),DAY)>90,

DATEDIFF('CLM Report'[Expires].[Date],TODAY(),DAY)<=180), "6 Months",

IF(AND(DATEDIFF('CLM Report'[Expires].[Date],TODAY(),DAY)>180,

DATEDIFF('CLM Report'[Expires].[Date],TODAY(),DAY)<=365), "12 Months",

"Outside of the Range")

))

 Below is the resulf of above formula and this is not correct since the 7 count is expiring in 2022, not 2023 (future dates).

Could you help please.

[v-rongtiep-msft · Accepted solution]

Hi @beginner_user08 ,

I hvae created a simple sample, please refer to my pbix file to see if it helps you.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.