Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count of Rows if The Date is before Today

Hi,

 

Most probably, it is super easy, but I don't know how to do this in one formula. Please help.

 

I have a column 'ClientEndDate' with empty values (not a client), dates in the future (our current clients), dates in the past (our lost clients).

 

I need to calculate the number of lost clients.

 

I understand that I need to do the following steps:

 

  1. Make sure the ClientEndDate is not Blank
  2. compare ClientEndDate <= TODAY()
  3. Calculate count of rows if the two abovementioned conditions are met.

 

Please help me to create a formula.

 

Thanks 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Measure = COUNTROWS(FILTER('Table',NOT(ISBLANK([ClientEndDate]))&&[ClientEndDate]<TODAY()))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

 

Share a sample dataset and show the expected result on that sample


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

Measure = COUNTROWS(FILTER('Table',NOT(ISBLANK([ClientEndDate]))&&[ClientEndDate]<TODAY()))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

For some reason the formula you provided is counting more than the dates prior to today. I have a  column ("(*) BL Project Finish") in a table ("TASK") that has dates and I want to count the dates in that column that are before today. I tweeked your formulate to fit my data but it is counting more than the dates that are not blank and prior to TODAY. Here is the formula I am using:

BL Project Finish To Date = COUNTROWS(FILTER('TASK',NOT(ISBLANK([(*)BL Project Finish]))&&[(*)BL1 Finish]<TODAY()))
 
Am I missing something?
 



Anonymous
Not applicable

Greg, thank you so much! It worked perfectly well 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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