To find out expired row item that the date will be expired in the next determine month

[Anonymous]

Hello everyone, may I know how can I code if I wanted to find out what are the number of unique row that their date will be expired in the next determine month (based on todays date) ? For example, the picture below show that C00003 will be expired within the next 6 month based on todays date, however, C00001 is not. So the result will be one out of 2 will be expired. 

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Column:

Expires next month = 
var _date = DATE(Year(today()),Month(today())+2,1)-1
Return
Switch(True(),
[Contact Expiry Date]<=_date &&[Contact Expiry Date]>= today(), "Yes",
[Contact Expiry Date]> _date ,"No")

Count = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Contract Number] ),
    FILTER ( 'Table', [Expires next month] = "Yes" )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Yes !! thank you so much for helping out 😁 really appreciate that !!

[amitchandak]

@Anonymous , New column

Expire in next sxi month

var _date = date(year(today()), month(today())+6 , day(today())

return

Switch(True(),

[Contract Expiry Date] < _date && [Contract Expiry Date] >= today(), "With in 6 month"

[Contract Expiry Date] >= _date && , "After 6 Month")

[Anonymous]

Hi, thanks for your help, but not sure why it is not working