cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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
Not applicable

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

Super User

@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
Not applicable

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