How to count the number of records (row count) under next one month

[susheeltyagi]

Hi,

I am trying to find the formula inwhich select the item count for expiry date fall under next 30 days from the today date.

I mean display the number of item that will expire by next 30 days from today date.

Example: If 4 item will expire within next 30 days from today date then DAX or formula will retuen "4"

ProductTable

ItemID	ExpiryDate
188	2022-06-15 23:59
189	2022-01-15 23:59
368	2021-04-25 23:59
369	2021-05-29 23:59
366	2021-03-25 23:59
367	2021-06-25 23:59
49	2021-06-04 23:59
363	2021-07-25 23:59
364	2021-08-25 23:59

[Jihwan_Kim]

Hi, @susheeltyagi 

Please check the below picture and the sample pbix file's link down below.

Expiry within next 30 days =
VAR expirydate =
TODAY () + 30
RETURN
COUNTROWS ( FILTER ( 'Table', 'Table'[ExpiryDate] < expirydate ) ) + 0

https://www.dropbox.com/s/avx7gpnmz8zgock/susheel.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[susheeltyagi]

My Friend

Thanks for quick reply.

Its working now but its showing me the records from back date from today.

I need the number of record from today date to next 30 Days,

Expiry within next 30 days =
VAR expirydate = TODAY () + 30
RETURN
COUNTROWS ( FILTER ( MeasurementQuery, MeasurementQuery[SensorExpiry] < expirydate ))+ 0

[Jihwan_Kim]

Hi, @susheeltyagi 

Thank you for your feedback.

I am not sure what is your data type of the SensorExpiry Column. Is it date type? 

In my opinion, you need to change this data type in Power Query Editor, not in Power BI Table View.

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

[susheeltyagi]

Its working now but its showing me the records from back date also from today.

I need the number of record from today date to next 30 Days,

Expiry within next 30 days =
VAR expirydate = TODAY() + 30 RETURN
COUNTROWS ( FILTER ( MeasurementQuery, MeasurementQuery[SensorExpiry] < expirydate ))

[Jihwan_Kim]

Sorry, I don't understand your question.

Share your sample pbix file's link and let me know how your desired outcome looks like.

[susheeltyagi]

HI,

Sorry to bother you. Actually I need to display the number Sensors or Products that will expire in next 30 days. If any product has sensor expire date has Yesterday or less then today date that mean that have already been expired.

So we have Highlit on our Dashboard that these are number of Items will expire within next 30 days.

[Jihwan_Kim · Accepted solution]

Hi, @susheeltyagi 

Terribly sorry that I still cannot understand.

I have your sample data, but your expected result is using different data.

It does not help me to understand your question.

I assume you do not want to show/count the earlier date than today. Is it correct? But I still wonder that I am still wrong. Because in your initial question, your expected result was 4. And result 4 includes the earlier date than today.

Please check below whether my assumption is what you are looking for.

https://www.dropbox.com/s/avx7gpnmz8zgock/susheel.pbix?dl=0 

Expiry within next 30 days V2 =

VAR expirydate =

TODAY () + 30

RETURN

COUNTROWS ( FILTER ( 'Table', 'Table'[ExpiryDate] < expirydate && 'Table'[ExpiryDate]>=TODAY() ) )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM