Need help to get a measure from which I can get the number of entries which is below a specific date

[Pranav13]

Hello All,

I am looking to get a measure from which I can get the number of entries in the excel which falls below the specific date.

Also the specific date has to be determined with a measure from the current date (Today).

So in excel what data I have is like Column with category and a column of date and from this i need to extract the categories which comes below a specific date. For example the count of category before 10/10/2023.

[v-xinruzhu-msft · Accepted solution]

Hi @Pranav13 

Based on your description, you can try the following.

Measure = CALCULATE(COUNTROWS('Table'),'Table'[Date]<DATE(2022,2,22),'Table'[Date]<>BLANK())

Output

Best Regards!

Yolo Zhu

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

[v-xinruzhu-msft]

Hi,

Thanks for Ashish_Mathur 's concern about the problem and i want to offer some more inforamtion for user to refer to.

hello @Pranav13 , based on your descriprtion, you can refer to the following sample.

Sample data.

Create a measure

Measure = CALCULATE(COUNTROWS('Table'),OR('Table'[Date]<DATE(2022,2,22),'Table'[Date]=BLANK()))

Output

If you need to set the filter date to today(0, just replace the meaausre to the following.

CALCULATE(COUNTROWS('Table'),OR('Table'[Date]<TODAY(),'Table'[Date]=BLANK()))

Best Regards!

Yolo Zhu

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

[Pranav13]

Sorry . I don't want the NA/Blanks to be counted - The entries with dates only has to be counted

[v-xinruzhu-msft · Accepted solution]

Hi @Pranav13 

Based on your description, you can try the following.

Measure = CALCULATE(COUNTROWS('Table'),'Table'[Date]<DATE(2022,2,22),'Table'[Date]<>BLANK())

Output

Best Regards!

Yolo Zhu

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

[Pranav13]

So my need is to get the number of location entries from this 2 columns which is before 24-Feb-22

So to be exact My expected output from the measure is 9 from the example.

[Ashish_Mathur]

Share both columns together.  Why should the answer be 9?

[Pranav13]

IF Bonded - Bond Date else NA	Location of Monitor
24-Feb-22	1R-007
25-Feb-22	1R-008
26-Feb-22	1R-009
27-Feb-22	1R-010
24-Feb-22	1R-011
24-Feb-22	1R-012
24-Feb-22	1R-013
NA	1R-014
28-Dec-22	1R-015
24-Feb-22	1R-016
24-Feb-22	1R-017
24-Feb-22	1R-018
NA	1R-019
28-May-21	1R-020
28-Dec-21	1R-021
28-Dec-21	1R-022
28-Dec-21	1R-023
28-Dec-21	1R-024
28-Dec-21	1R-025
28-Dec-21	1R-026

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[Pranav13]

Thanks Ashish - i will try and let you know

[Pranav13]

Sorry the count i wish is 7 - as the dates which are before 24th February 2022 - there are 7 entries in the column. So whenever a date is given whichever comes below specific date will be counted and output is given

[Pranav13]

Next Column

Location of Monitor

1R-007

1R-008

1R-009

1R-010

1R-011

1R-012

1R-013

1R-014

1R-015

1R-016

1R-017

1R-018

1R-019

1R-020

1R-021

1R-022

1R-023

1R-024

1R-025

1R-026

[Pranav13]

Date Column

IF Bonded - Bond Date else NA

24-Feb-22

25-Feb-22

26-Feb-22

27-Feb-22

24-Feb-22

24-Feb-22

24-Feb-22

NA

28-Dec-22

24-Feb-22

24-Feb-22

24-Feb-22

NA

28-May-21

28-Dec-21

28-Dec-21

28-Dec-21

28-Dec-21

28-Dec-21

28-Dec-21

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.