cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors